LAE

 View Only
Back to discussions
Expand all | Collapse all

Applying a conversion to all fields from all columns

  • 1.  Applying a conversion to all fields from all columns

    Employee
    Posted 04-09-2019 09:08

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

    Originally posted by: 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.....


  • 2.  RE: Applying a conversion to all fields from all columns

    Employee
    Posted 04-09-2019 09:29

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

    Originally posted by: 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


  • 3.  RE: Applying a conversion to all fields from all columns

    Employee
    Posted 04-09-2019 10:48

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

    Originally posted by: 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.


  • 4.  RE: Applying a conversion to all fields from all columns

    Employee
    Posted 04-09-2019 15:26

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

    Originally posted by: gmullin

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


  • 5.  RE: Applying a conversion to all fields from all columns

    Employee
    Posted 04-10-2019 01:12

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

    Originally posted by: 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


  • 6.  RE: Applying a conversion to all fields from all columns

    Employee
    Posted 04-10-2019 05:10

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

    Originally posted by: 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


  • 7.  RE: Applying a conversion to all fields from all columns

    Employee
    Posted 04-10-2019 05:31

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

    Originally posted by: 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.


  • 8.  RE: Applying a conversion to all fields from all columns

    Employee
    Posted 04-10-2019 06:43

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

    Originally posted by: 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.


  • 9.  RE: Applying a conversion to all fields from all columns

    Employee
    Posted 04-10-2019 08:35

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

    Originally posted by: 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.


  • 10.  RE: Applying a conversion to all fields from all columns

    Employee
    Posted 04-11-2019 00:37

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

    Originally posted by: 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


  • 11.  RE: Applying a conversion to all fields from all columns

    Employee
    Posted 04-11-2019 00:38

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

    Originally posted by: sto

    Thank you gmullin. The updated brg works very well, but as I have several millions of lines with 17 columns, the '2 columns output' node gives a result which exceed the Lavastrom row limit (10M)