Creating New DataFlow from Zips2Fips DataSet and ODBC DataSet
I have USA map card that I want to drill down to counties. I got Zips2Fips DataSet from DomoSolutions that I have to join to my DataSet. I tried to do that through Magic ETL joining these two DataSets matching ZipCodes, but I am getting empty rows.
What could be the problem?
Thank you,
Marko.
Best Answer
-
Ok, give this a shot. If it works then use this as your output dataset and then replace the EpicProduction dataset with this in your ETL.
SELECT *, CONVERT(LEFT(RTRIM(LTRIM(`AcctZipCode`)),5), UNSIGNED INTEGER) as 'CleanZip'
FROM epicproductionThis should let you join directly to the ZIP field (hopefully).
0
Answers
-
Hi, User05404,
Without seeing the file itself; the only thing I could recommend is check the type of data you're trying to join. Are they the same data type? Same length? etc...
Best wishes,
Marc Ha
3 -
I agree with @Marc_H, it sounds like a data type mismatch. Are you joining use the ZIP or ZIP_STRING field from that dataset?
Also, could you provide a couple of zip codes that it returned blank for? Maybe they were 9 digit hyphenated zip codes or had an odd character in the field?
With more detail we should be able to tell you more.
Sincerely,
ValiantSpur
3 -
I am matching ZIP_STRING field since I can't use ZIP, it says they are different data types. Here is a screenshot.
Thank you.
1 -
Hi, User05404,
Can you give Valiant and I a sample from your ZIP column in the ODBC data set you're using?
What you can try is convert your string ZIP column into a Whole Number and join it on ZIP from Zips2Fips data set - assuming your ZIP column from ODBC data set is 5 char. I haven't tested this, so please keep us updated.
Warm regards,
Marc Ha
1 -
I already tried to convert string from my ODBC to whole number but I keep receiving an error "Failed to convert data 94563 to type integer for column AcctZipCode".
Thank you for your help.
1 -
I don't know why I am not able to convert AcctZipCode to WholeNumber.
0 -
It sounds like you have some non-numeric fields in your data that isn't allowing for conversion to whole number. It's going to be a bit more tricky in ETL than a SQL transform but you should be able to do the following.
If say you were able to identify that you had "-" in your zip codes for something like 96875-2486 then you could do the following to strip it out:
Use the "Replace Text" action under Edit Data. Make sure you have the offending character in the "2" option as shown below:
Give this a shot and see if after stripping out character you can then convert your column.
Sincerely,
ValiantSpur
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.0 -
Unfortunately doesn't work!
0 -
Ok, at this point I would switch over to a SQL transform and we'll have to get our hands dirty.
I would use the following to identify what non numeric zips are currently in my data:
SELECT `AcctZipCode` FROM EpicProduction WHERE `AcctZipCode` NOT IN (SELECT `AcctZipCode`
FROM EpicProduction WHERE `AcctZipCode` REGEXP '^[0-9]+$')This will return all of the ZIP codes that have non-numeric values that would cause the conversion to fail.
Let me know if you get any results from this and we can go from there.
0 -
Here is my outpu. Thank you.
1 -
Ok, give this a shot. If it works then use this as your output dataset and then replace the EpicProduction dataset with this in your ETL.
SELECT *, CONVERT(LEFT(RTRIM(LTRIM(`AcctZipCode`)),5), UNSIGNED INTEGER) as 'CleanZip'
FROM epicproductionThis should let you join directly to the ZIP field (hopefully).
0 -
Hi everybody, thank you for your help.
I finally get it done. I tried to join Converted dataset (ValiantSpur gave me an advice) with Zips2Fips through ETL, which didn't work. I tried Blend, and it worked. I don't know what is the problem with ETL, anyways, I got what I needed.
Thank you,Marko.
2
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 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
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 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