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

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

Find more posts tagged with

Accepted answers

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

All comments

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