Pull Data From One Column Based On Condition From a Different Column

leeloo_dallas

Hey guys!

I've been at this for a while, Magic ETL, Analyzer, Google, nothing seems to answer the following (maybe it's because I'm new)

I can't seem to find a means to pull data from a numerical column based on the condition of a categorical column without aggregating as a sum. Could anyone help me with this without changing the values from the numerical column?

 

My table is like the following:

Column A	Column B	Date
234	2019	2019-06-13
0	2019	2019-09-30
16	2019	2019-12-21
1	2020	2020-06-13
1	2020	2020-09-30
32	2020	2019-12-21

 

And I want to turn it into the following:

Previous	Current	Date
234	n/a	2019-06-13
0	n/a	2019-09-30
16	n/a	2019-12-21
n/a	1	2020-06-13
n/a	1	2020-09-30
n/a	32	2019-12-21

 

I'm trying to use the advanced comparative gauge tool in the Analyzer.

Any tips or ideas, I am all ears *ahem* ...eyes

GrantSmith

I’m on mobile and the dojo won’t let me edit my reply but the formula should look like this for prior

CASE WHEN `Column B` = YEAR(CURRENT_DATE) - 1 THEN `Column A` END

 

and this for current

CASE WHEN `Column B` = YEAR(CURRENT_DATE) THEN `Column A` END

 

GrantSmith

Hi @user052846 

CASE statements are your friend in this  case. Assuming you’re looking for data from the prior year you can use logic to conditionally set a new column value using a case statement in a beast mode. 

For your current Column:

CASE WHEN `Column B` YEAR(CURRENT_DATE) THEN `Column A` END

Previous Column

CASE WHEN `Column B` YEAR(CURRENT_DATE) - 1 THEN `Column A` END

 

since no ELSE condition is specified in the case statement it defaults to NULL (blank)