0

I have a dataset with more than 20 columns. It consists of transactions in cash and non-cash form for the year 2020 and 2021.

If the transaction date lies in 2020, its amount is mentioned.[Blue color] Then for 2021, a random 100 amount is assigned.

Similarly, if the transaction date is in 2021, its amount is mentioned. [Pink color] Then for 2020, a random 100 amount is assigned. enter image description here

REQUIREMENT - For a person , if cash and non cash method exists , can it be combined/ merged as SINGLE row i.e [Blue and Pink figures in a single row] using BigQuery

REQUIRED RESULT - enter image description here

Improve this question

1 Answer 1

Reset to default
1

Tried to replicate your scenario here:

with raw_data as(

select 'MP' as Name, 10 as ID, 'Cash' as Method, 'SAN JOSE' AS CITY, 'CA' AS STATE, '2020-01-20' AS TRANSACTION_DATE, 2000 AS Q1_2020,1200 AS Q2_2020, 2000 AS Q3_2020,5080 AS Q4_2020, 100 AS Q1_2021,100 AS Q2_2021, 100 AS Q3_2021 UNION ALL
select 'MP' as Name, 12 as ID, 'Non-Cash' as Method, 'san jose' AS CITY, 'CA' AS STATE, '2021-02-03' AS TRANSACTION_DATE, 100 AS Q1_2020,100 AS Q2_2020, 100 AS Q3_2020,100 AS Q4_2020, 2000 AS Q1_2021,3000 AS Q2_2021, 4000 AS Q3_2021 UNION ALL
select 'KS' as Name, 11 as ID, 'Cash' as Method, 'LAKE FOREST' AS CITY, 'CA' AS STATE, '2020-02-12' AS TRANSACTION_DATE, 2000 AS Q1_2020,4000 AS Q2_2020, 5000 AS Q3_2020,2200 AS Q4_2020, 100 AS Q1_2021,100 AS Q2_2021, 100 AS Q3_2021 UNION ALL
select 'KS' as Name, 34 as ID, 'Non-Cash' as Method, 'lake forest' AS CITY, 'CA' AS STATE, '2021-04-03' AS TRANSACTION_DATE, 100 AS Q1_2020,100 AS Q2_2020, 100 AS Q3_2020,100 AS Q4_2020, 3000 AS Q1_2021,1500 AS Q2_2021, 2500 AS Q3_2021 UNION ALL
select 'GS' as Name, 21 as ID, 'Cash' as Method, 'EDITION' AS CITY, 'NJ' AS STATE, '2020-03-20' AS TRANSACTION_DATE, 9000 AS Q1_2020,1000 AS Q2_2020, 2300 AS Q3_2020,1200 AS Q4_2020, 100 AS Q1_2021,100 AS Q2_2021, 100 AS Q3_2021 UNION ALL
select 'GS' as Name, 22 as ID, 'Non-Cash' as Method, 'edition' AS CITY, 'NJ' AS STATE, '2021-05-12' AS TRANSACTION_DATE, 100 AS Q1_2020,100 AS Q2_2020, 100 AS Q3_2020,100 AS Q4_2020, 2300 AS Q1_2021,3200 AS Q2_2021, 4500 AS Q3_2021 UNION ALL
select 'JSK' as Name, 27 as ID, 'Cash' as Method, 'Piscataway' AS CITY, 'NJ' AS STATE, '2020-02-13' AS TRANSACTION_DATE, 2345 AS Q1_2020,5600 AS Q2_2020, 1200 AS Q3_2020,2100 AS Q4_2020, 100 AS Q1_2021,100 AS Q2_2021, 100 AS Q3_2021 
),

Cleaned_data as (
SELECT Name,STATE,MAX(Q1_2020) Q1_2020, MAX(Q2_2020) Q2_2020,MAX(Q3_2020) Q3_2020, MAX(Q4_2020) Q4_2020,
MAX(Q1_2021) Q1_2021, MAX(Q2_2021) Q2_2021, MAX(Q3_2021) Q3_2021 
FROM raw_data 
GROUP BY Name,STATE)

select rd.Name, rd.ID,rd.Method, rd.CITY,rd.STATE, rd.TRANSACTION_DATE,

 case when rd.Method = 'Non-Cash' then 0 else  CL.Q1_2020 end as Q1_2020,
 case when rd.Method = 'Non-Cash' then 0 else  CL.Q2_2020 end as Q2_2020,
 case when rd.Method = 'Non-Cash' then 0 else  CL.Q3_2020 end as Q3_2020,
 case when rd.Method = 'Non-Cash' then 0 else  CL.Q4_2020 end as Q4_2020,
 case when rd.Method = 'Non-Cash' then 0 else  CL.Q1_2021 end as Q1_2021,
 case when rd.Method = 'Non-Cash' then 0 else  CL.Q2_2021 end as Q2_2021,
 case when rd.Method = 'Non-Cash' then 0 else  CL.Q3_2021 end as Q3_2021
from Cleaned_data  CL 
left join raw_data rd 
on rd.name= CL.Name and rd.STATE=CL.STATE;

Here it output what I got:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.