Changing a value to an integer in the ETL

Golfoholic · 2021-10-11T19:23:45+00:00

There was an error rendering this rich post.

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.

Find more posts tagged with

Magic ETL

Data Flows

Accepted answers

GrantSmith

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.

All comments

GrantSmith

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

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

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.

Golfoholic

@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

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.