How to mix and match Data
|
Hi Folks,
Need your help pls.
2 Questions -
1) In the table above, you could see that 1300 is mapped to city 'D' but in last row it is blank.
How do I map it using beast mode or Magic ETL to fill by Lookup in the same column.
Req: Lookup in 'Number' and fill Blanks in 'City'
2) 5th Row contains E in Number and 1400 in City. How do I exchange it to the right position using beast mode or Magic ETL?
Req: Case when 'Number' contains String value then City.
Case when 'City' contains Numeric value then 'Number'
Thanks in Advance!
Best Answer
-
First, we should solve problem #2. Then, we'll create a canonical lookup table from
Number
toCity
to solve problem #1.
Use an Add Formula tile to make two new columns,Canonical Number
andCanonical City
like so:Canonical Number
formula:NULLIF(STR_DIGITS(CASE WHEN City BETWEEN '0' AND '999999' THEN City ELSE Number END), '')
Canonical City
formula:NULLIF(STR_REMOVE_DIGITS(CASE WHEN Number BETWEEN 'A' AND 'zzzzzz' THEN Number ELSE City END), '')
Now use a Group By tile to make the lookup table. Our one grouping column will be
Canonical Number
, and our one aggregate will be the First non-null value ofCanonical City
; we'll call this theLookup City
.
Now use the Join Data tile to Inner Join the original table (the output of the Add Formula) to the lookup table (the output of the Group By) on theCanonical Number
column.
TheCanonical Number
column and theLookup City
column in the result of the Join Data tile have the final values you want.
The whole transform has this shape:Randall Oveson <randall.oveson@domo.com>
2
Answers
-
First, we should solve problem #2. Then, we'll create a canonical lookup table from
Number
toCity
to solve problem #1.
Use an Add Formula tile to make two new columns,Canonical Number
andCanonical City
like so:Canonical Number
formula:NULLIF(STR_DIGITS(CASE WHEN City BETWEEN '0' AND '999999' THEN City ELSE Number END), '')
Canonical City
formula:NULLIF(STR_REMOVE_DIGITS(CASE WHEN Number BETWEEN 'A' AND 'zzzzzz' THEN Number ELSE City END), '')
Now use a Group By tile to make the lookup table. Our one grouping column will be
Canonical Number
, and our one aggregate will be the First non-null value ofCanonical City
; we'll call this theLookup City
.
Now use the Join Data tile to Inner Join the original table (the output of the Add Formula) to the lookup table (the output of the Group By) on theCanonical Number
column.
TheCanonical Number
column and theLookup City
column in the result of the Join Data tile have the final values you want.
The whole transform has this shape:Randall Oveson <randall.oveson@domo.com>
2 -
You could recreate those two fields as follows and then drag them into your card in place of the current 2 fields:
- City_2: CASE WHEN `Number` = 1300 THEN 'D' ELSE `City` END
- Number_2: CASE WHEN `Number` = 'E' THEN 1400 ELSE `Number` END
If I solved your problem, please select "yes" above
0 -
@Rupak I second @colemenwilson's recommendation of recreating the fields. I would do this in MagicETL using the Formula Tile. If you don't want to specify every value to swap, you could use a regular expression to match patterns based on whether
`Number`
contains alpha characters and`City`
contains numeric characters.City_2:
CASE WHEN REGEXP_LIKE(`Number`,'[a-zA-Z].*]') then `Number` else `City` END
Number_2:
CASE WHEN REGEXP_LIKE(`City`,'[0-9].*') then `City` else `Number` END
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 299 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 681 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 9 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
- 33 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive