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.
Best 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.
2
Answers
-
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)1 -
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'
ENDOnce the BEASTMODE is created, you can use it in the filter or table column as needed.
Best wishes,
Marc H.
1 -
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.
2
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 472 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 202 Visualize
- 255 Beast Mode
- 2.1K Charting
- 12 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 180 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive