Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Applying a conversion to all fields from all columns

  1. #1
    Contributor
    Join Date
    Feb 2018
    Location
    Paris, France
    Posts
    20

    Default Applying a conversion to all fields from all columns

    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. #2
    Lavastorm Employee gmullin's Avatar
    Join Date
    May 2014
    Location
    Chicago
    Posts
    185

    Default

    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.

    Code:
    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. #3
    Contributor
    Join Date
    Feb 2018
    Location
    Paris, France
    Posts
    20

    Default

    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. #4
    Lavastorm Employee gmullin's Avatar
    Join Date
    May 2014
    Location
    Chicago
    Posts
    185

    Default

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

  5. #5
    Contributor
    Join Date
    Feb 2018
    Location
    Paris, France
    Posts
    20

    Default

    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. #6
    Lavastorm Employee gmullin's Avatar
    Join Date
    May 2014
    Location
    Chicago
    Posts
    185

    Default

    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
    Last edited by gmullin; 04-10-2019 at 03:34 PM. Reason: Updated brg

  7. #7
    Contributor
    Join Date
    Feb 2018
    Location
    Paris, France
    Posts
    20

    Default

    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. #8
    Lavastorm Employee stonysmith's Avatar
    Join Date
    Nov 2006
    Location
    Grapevine Tx
    Posts
    799

    Default

    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. #9
    Lavastorm Employee gmullin's Avatar
    Join Date
    May 2014
    Location
    Chicago
    Posts
    185

    Default

    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. #10
    Contributor
    Join Date
    Feb 2018
    Location
    Paris, France
    Posts
    20

    Default

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •