Creating New DataFlow from Zips2Fips DataSet and ODBC DataSet

mstorne
mstorne Member
in Magic ETL

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

  • Valiant
    Valiant Coach
    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 epicproduction

    This should let you join directly to the ZIP field (hopefully).

     

Answers

  • Marc_H
    Marc_H Member

    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

     

  • Valiant
    Valiant Coach

    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

  • mstorne
    mstorne Member

    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.

  • Marc_H
    Marc_H Member

    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

     

    hi.PNG

  • mstorne
    mstorne Member

    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.

  • mstorne
    mstorne Member

    I don't know why I am not able to convert AcctZipCode to WholeNumber. 

  • Valiant
    Valiant Coach

    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:

    image.png

    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.

  • mstorne
    mstorne Member

    Unfortunately doesn't work! 

     

    Screenshot from 2018-01-30 12-28-16.png

  • Valiant
    Valiant Coach

    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.

  • mstorne
    mstorne Member

    Here is my outpu. Thank you.

     

    Screenshot from 2018-01-30 14-11-55.png

  • Valiant
    Valiant Coach
    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 epicproduction

    This should let you join directly to the ZIP field (hopefully).

     

  • mstorne
    mstorne Member

    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.

Categories