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

leeloo_dallas
leeloo_dallas Member
in Magic ETL

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 AColumn BDate
23420192019-06-13
020192019-09-30
1620192019-12-21
120202020-06-13
120202020-09-30
3220202019-12-21

 

And I want to turn it into the following:

PreviousCurrentDate
234n/a2019-06-13
0n/a2019-09-30
16n/a2019-12-21
n/a12020-06-13
n/a12020-09-30
n/a322019-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

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach

    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)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories