Magic ETL - Add a "Flag" column based on comparing a date in one column to current date

swagner
swagner Contributor
in Magic ETL

In my data I have a date column.  I'd like to create a column via Magic ETL (want to use in a filter) that looks at the date a determines if it is in the past or in the future.  Here is a short video that explains.

 

From an Excel formula standpoint, it would be:  =IF([@[Last Update]]>TODAY(),"Future","Past")

 

I tried with value mapper, but no luck.

 

Best Answer

  • Marc_H
    Marc_H Member
    Answer ✓

    Hi, Swagner,

     

    I had a chance to play with ETL today and got something put together. Hopefully this will give you what you need; if not, give you some ideas.

     

    - The Date - Current is just using the Date Operations to give you a value. This will indicate how many 'Days until Date'. Take your 'Last Update' field (-) Current Date value.

    - Filter as needed (on the field created by the Date Operations action) and compare it to the value 0 - which is current date.

    - Give a flag for each group of filters

    - Apend

    - Combine

     

    This is somewhat vague, but feel free to let me know if you need some clarification.

     

    Best wishes,

     

     

    Marc H.

    Capture.PNG

Answers

  • Tomo
    Tomo Contributor

    Hi.

     

    1. Give today's date as a numeric value in the input data. [20180109] (A)
    2. Convert the date you want to compare to text type. [2018-01-10] (B)
    3. Retrieve only numbers from (B). [20180110] (C)
    4. Convert (C) to numeric type. [20180110] (D)
    5. Find the difference between (A) and (D). 20180109 - 20180110 = [1] (E)
    6. Extract 1 or more lines with (E) by a filter. (F)
    7. Extract the line with (E) less than 0 with the filter. (G)
    8. Add a column to (F) and set the value "Future". (H)
    9. Add a column to (G) and set the value "Past". (I)
    10. Combine the (H) and (I) lines. (J)

     

    ====

    1. 入力データに今日の日付を数値で持たせます。[20180109] (A)
    2. 比較したい日付をテキスト型に変換します。[2018-01-10] (B)
    3. (B)から数値のみを取り出します。[20180110] (C)
    4. (C)を数値型に変換します。[20180110] (D)
    5. (A)と(D)の差を求めます。20180109 - 20180110 = [1] (E)
    6. (E)が1以上の行をフィルターで抜き出します。(F)
    7. (E)が0以下の行をフィルターで抜き出します。(G)
    8. (F)にカラムを追加して値"Future"をセットします。 (H)
    9. (G)にカラムを追加して値"Past"をセットします。 (I)
    10. (H)行と(I)行を結合します。(J)

  • Marc_H
    Marc_H Member

    Hi, Swagner,

     

    ---From an Excel formula standpoint, it would be:  =IF([@[Last Update]]>TODAY(),"Future","Past")---

     

    If you're willing to go the BEASTMODE route; you can create a flag column by using the CASE function. - This is assuming the field 'Last Update' is a DATE field.

     

    CASE WHEN `LAST UPDATE` > CURRENT_DATE() THEN 'FUTURE'
    ELSE 'PAST'
    END

     

    Once the BEASTMODE is created, you can use it in the filter or table column as needed.

     

    Best wishes,

     

    Marc H.

  • Marc_H
    Marc_H Member
    Answer ✓

    Hi, Swagner,

     

    I had a chance to play with ETL today and got something put together. Hopefully this will give you what you need; if not, give you some ideas.

     

    - The Date - Current is just using the Date Operations to give you a value. This will indicate how many 'Days until Date'. Take your 'Last Update' field (-) Current Date value.

    - Filter as needed (on the field created by the Date Operations action) and compare it to the value 0 - which is current date.

    - Give a flag for each group of filters

    - Apend

    - Combine

     

    This is somewhat vague, but feel free to let me know if you need some clarification.

     

    Best wishes,

     

     

    Marc H.

    Capture.PNG

Categories