Results 1 to 10 of 11

Thread: Applying a conversion to all fields from all columns

Hybrid View

  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

Posting Permissions

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