Changing a value to an integer in the ETL

Golfoholic
Golfoholic Member
in Magic ETL

I have a column that uses an 'x' as a flag, any row that isn't flagged with an 'x' is left as an empty string. What I need is to replace the 'x' with a '1' to be used in a later count. this should be straightforward but I keep getting my wires crossed with the order of operations.

image.png


Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can use the value mapper to map the 0 to an empty string (click the gear icon on the replacement field - select empty string) before you use the Set Column Type.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @user048760

    If you have Magic ETL 2.0 you can use a formula tile and a case statement to swap it out to a 1 in a new column:

    CASE WHEN `ExcludeOnline` = 'x' THEN 1 ELSE 0 END

    Alternatively you can do this as a Beast Mode as well if you need it just on the card.

    If you don't have Magic 2.0, you can wait when it comes GA later this month (possibly later this week)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    GrantSmith Coach
    edited October 2021

    Another option is to utilize a Value Mapper tile to search for values of 'x' and replace it with 1 or 0 (default value) if it's not found. Then use a Set Column Type tile to convert it from a string to a numeric.

    You can copy and paste the following code into your dataflow to replicate this logic (tweak your dataset and column names)

    {"contentType":"domo/dataflow-actions","data":[{"name":"Value Mapper","id":"d27294fc-eb8d-4c09-a51f-d576fd9826ee","type":"ValueMapper","gui":{"x":324,"y":264},"dependsOn":["7e6a1b87-1ec5-4ee9-bf37-650d2f4093c3"],"removeByDefault":false,"notes":[],"fieldToUse":"str","targetField":"new number","unmappedBehavior":"WRITE_DEFAULT","default":"0","targetType":"STRING","mappings":[{"to":"1","from":"x"}]},{"name":"Set Column Type 1","id":"82ff86f6-cf55-4305-9233-9b1a038962ed","type":"Metadata","gui":{"x":432,"y":264},"dependsOn":["d27294fc-eb8d-4c09-a51f-d576fd9826ee"],"removeByDefault":false,"notes":[],"fields":[{"name":"str","type":"LONG"}]}]}


    This method should work with Magic 1.0 or Magic 2.0

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    Value mapper only operates on TEXT. So after you finish you value mapping you'd need to use a tile to Change the Data Type to integer.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Golfoholic
    Golfoholic Member

    @jaeW_at_Onyx @GrantSmith Everything is looking good. The counts that I was looking for are reflecting correctly. There is just one thing that is a little off. I see a good amount of nulls which I expected, but what is weird is there are nulls and then there are zeroes. I would want those that are showing 0 to show reflect null, but not sure how i can easily apply that change now that the column is an integer and not a text.

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can use the value mapper to map the 0 to an empty string (click the gear icon on the replacement field - select empty string) before you use the Set Column Type.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories