We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Applying a conversion to all fields from all columns

Comments

10 comments

  • Avatar
    Infogix Support
    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
    0
    Comment actions Permalink
  • Avatar
    Infogix Support
    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.
    0
    Comment actions Permalink
  • Avatar
    Infogix Support
    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.
    0
    Comment actions Permalink
  • Avatar
    Infogix Support
    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
    0
    Comment actions Permalink
  • Avatar
    Infogix Support
    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
    0
    Comment actions Permalink
  • Avatar
    Infogix Support
    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.
    0
    Comment actions Permalink
  • Avatar
    Infogix Support
    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.
    0
    Comment actions Permalink
  • Avatar
    Infogix Support
    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.
    0
    Comment actions Permalink
  • Avatar
    Infogix Support
    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
    0
    Comment actions Permalink
  • Avatar
    Infogix Support
    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)
    0
    Comment actions Permalink

Please sign in to leave a comment.