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

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.

Find more posts tagged with

Accepted answers

Marc_H

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.

All comments

Tomo

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

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

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.