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)

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