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
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive