Changing a value to an integer in the ETL

user048760
user048760 βšͺ️

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.


Best Answer

  • GrantSmith
    GrantSmith Indiana πŸ”΄
    Accepted 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.

Answers

  • GrantSmith
    GrantSmith Indiana πŸ”΄

    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)

  • GrantSmith
    GrantSmith Indiana πŸ”΄
    edited October 11

    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

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀

    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.

  • user048760
    user048760 βšͺ️

    @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.