Applying a conversion to all fields from all columns

[sto]

I have several columns with Unix time values in this format: "1545711033395,0000000000"
I need to translate them into YYYY-MM-DD HH:MM:SS

Pretty easy when I'm working with only one column DATECREATION:
ConvertedTime_date = timestamp(long(substr(DATECREATION,0,strFind(DATEC REATION,","))))

But I can't find a way to apply that conversion to all fields in all columns.

Unsuccessfully tried with inputFields(1) and some While loops.....

[gmullin]

Is 1545711033395 the timestamp value? What is 0000000000? Are you expecting the value you wrote to translate to 2018-12-25 04:10:33?

If you just need to convert the first values from timestamp to datetime, then this code should work.

node:Static_Data
bretype:core::Static Data
editor:sortkey=5cacc4fb6bd556f6
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
TimeStampValue
"1545711033395,0000000000"
EOX
editor:XY=180,190
end:Static_Data

node:Filter
bretype:core::Filter
editor:sortkey=5cacc54239b00e92
input:@40fd2c74167f1ca2/=Static_Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
splitTime = 'TimeStampValue'.split(",")

dateVal = long(splitTime[0])
#timeVal = long(splitTime[1])

emit timestamp(dateVal) as "DateValue"

EOX
editor:XY=290,190
end:Filter

[sto]

thx gmullin,

Yes, 1545711033395 is an exemple of my data. The 000000000000 are useless but are present.

Unfortunately I can't test your code right now. But does it convert all columns?
I have 17 columns full of Unix time data. My request is to convert ALL fields of ALL columns.

[gmullin]

What I wrote only converts a single comment. If your 17 columns are consistent then you can repeat the logic across all 17.

[sto]

That's exactly my problem: how to convert all columns in a dynamic way? Names and number of columns may change depending of the extracts

[gmullin]

Have a look at the attached brg. It will cater for up to 10 fields. If you think you'll get more, 30, 40, 50 etc. I labelled 2 nodes that you will need to add values to to increase how many fields you might expect.

What it will do is, convert all fields to common names. Add in some dummy fields. As you'll see I started with a dataset of 4 fields, so I had to add 6 dummy ones for the Filter node that performs the timestamp conversion to work. When it's done we'll convert the names back to the original and drop the extra dummy fields I had added.

It's not the most elegant code, but it should handle varying amount of fields you are going to encounter and it will perform the same logic on all of them.

UnknownNumberOfFields.brg

[sto]

Thank you very much gmullin. It works very well.
But as you said, it is not dynamic. I thought that a 'while' loop and using "inputFields(1)" to get the list of fields will make it completely dynamic. But I never found how to write it.

[stonysmith]

You can easily use inputFields() to get a list of columns and then a while{} to process the values
The challenge here is that within BrainScript, you can not dynamically set OUTPUT column names.

x=inputFields(1)[5] #returns the name of the fifth field
fn="1:"+x #build field name
v=field(fn) #get field value by name

but, there is NO way to do this, even if the field already exists on the output
emit 1282821 as fn # output value to a variable column name - not allowed

emit ONLY allows string constants as field names..
emit 1282821 as "oldfieldname"

================
You CAN accomplish the desired effect if you move over to Python or Java, but that would require knowledge of one of those two languages.

[gmullin]

Like Stony says, you'll always have a problem trying to emit columns when you don't know how many or what they'll be called. In saying that, a second option is to emit all field names and field values side by side (using inputFields) and then pivot them using Data to Names node.

I updated the brg in my post above to include a second option on how to achieve this. You might find it's more dynamic for your needs. I believe either work based on your description, but option 1 you would have to set some ceiling on the max number of columns you could handle.

[sto]

Thank you stonysmith. I'm currently migrating to data3sixty. I'll try to find the right Python code
By the way, hope the D3sixty forum will be as reactive as the Lavastorm one