LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dcoll

Hello! Sorry if this is an easy question, but I am quite new to Lavastorm and I can't get the solution for this.

I have data like this:


Name Fieldx FieldY FieldZ

John 00 XX 00
Andrew XX 00 00
Mary 00 00 00


The fields are dynamic, can have different names depending on the input and there are like 20 fields like those and other times 40, so putting all the names each time is not a possibility. How could I transform that 00's (which are strings) to an Integer '0' or a NULL value?

Thank you very much in advance.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

So here are two ways, depending on what the rest of the data looks like.

If the columns all contain numbers (instead of XX), then you could use a Data Analyzer with Convert Leading Zeros set to true. That would actually convert all Field* fields to integers.

If you want to do a replace of 00's with 0's across ALL columns/rows, then a Replace Text could work. But keep in mind that this would turn 100 into 10, and so forth. So this would only work if you don't have other instances of 00's that you want to keep.

These columns, do they have any sort of pattern that we could identify them? Do they all start with the same prefix, or are in a certain format? If so, we could split out those fields and treat them separately while keeping the other fields untouched.
Attachments:
convert00.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dcoll

Thank you for your quick reply!

The Data Analyzer Node don't work in that way as the field is recognised as String, because the other values follow no pattern. They can be large numbers or a string.

The Replace Text option don't work either, there can be large numbers which may have the 00 number inside.

Thanks again

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Ok, here's another solution offered by Stony. But it does come with a warning that it's mainly good for "small" data sets. So if you have have more than 100k rows of data, it's really not ideal....

The premise is that we first pivot the data so that each value (per column per row) becomes a record. So if you have 10 rows with 4 columns, you'll now have 40 rows. We can then operate on each row, whether it'd be with replace, regular expressions, or something else. Then we pivot the prepped data back into its original form. See attached.
Attachments:
convert00.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dcoll

This works perfectly!

We don't plan to have more than 30 rows, so it fits

Thank you very much for your quick help!

Cheers!