Data360 Analyze

 View Only

  • 1.  Data Transformation

    Posted 01-18-2023 21:05
      |   view attached
    I have come across this problem with the data that I'm working with. The data transformation was raised on separate post previously and it was successfully transformed but it was excluded one important element of the data is the SourceFileName to also appended on every set of data just like how it shows on the attached excel file. 

    Note:
    1 source file - contains approx. 100K+ lines of data
    Total Files - contains approx. 50 mil + lines of data




    ------------------------------
    Tevita Fainga
    Tonga Communications Corporation
    Fasi-moe-Afi
    ------------------------------

    Attachment(s)

    xlsx
    INET Data Transformation.xlsx   12 KB 1 version


  • 2.  RE: Data Transformation

    Employee
    Posted 01-19-2023 11:45

    I'm not sure what the input to your node is.  but could you just add this line to the Script?

    emit 'FileSourceName'

    Ernest



    ------------------------------
    Ernest Jones
    Precisely Software Inc.
    PEARL RIVER NY
    ------------------------------

    Original Message


  • 3.  RE: Data Transformation

    Posted 01-19-2023 14:39
    Modify script by adding emit 'FileSourceName'



    The output data below and after that I use Transpose node to transpose the data which is shown on the Dataflow below.



    Dataflow 

    So, I expect the data to transpose into this with the FileName to include at the end.

    _DateTimeStamp Acct_Session_Time Acct_Status_Type Event_Timestamp pppoe_session_id circuit_id_tag Acct_Authentic Acct_Input_Octets Acct_Output_Octets Client_MAC_Address Acct_Unique_Session_Id Stripped_User_Name Timestamp FileSourceName
    Sun Jan  1 00:00:00 2023 300 Interim-Update Jan  1 2023 00:00:00 +13 54402 FAN-MSAN1 atm 01/14:0.34 RADIUS 3243041 106667828 7c8b.cae9.db7c ec12651825b9e2368b0c38fd3ea728aa matangi_tonga 1672484400 detail-20230101-00
    Sun Jan  1 00:00:02 2023 12600 Interim-Update Jan  1 2023 00:00:02 +13 51576 NULL RADIUS 300708733 1194020509 0c80.6372.2986 ab3a8883eb7badf6a4e4a060f0e9cda6 jwtonga 1672484402 detail-20230101-00
    Sun Jan  1 00:00:02 2023 14999 Interim-Update Jan  1 2023 00:00:02 +13 42269 TON-MSAN1 atm 01/28:0.34 RADIUS 73299 605435 48f8.b3f6.f537 faf8ecff936b38ba590fd95e7c33508e stuutafaiva 1672484402 detail-20230101-00


    ------------------------------
    Tevita Fainga
    Tonga Communications Corporation
    Fasi-moe-Afi
    ------------------------------

    Original Message


  • 4.  RE: Data Transformation

    Posted 01-19-2023 14:44
    Input source



    ------------------------------
    Tevita Fainga
    Tonga Communications Corporation
    Fasi-moe-Afi
    ------------------------------

    Original Message


  • 5.  RE: Data Transformation

    Employee
    Posted 01-20-2023 10:49
    I think I now understand the question.  I put together a dataflow to solve it, and since it can't be attached, I'll describe what I did.
    • Create Data - your input data.
    • Add a Record Index - We need a way of identifying a record id.  When looking at the data I made a guess that each time we see a _DataTimeStamp key value, we are on a new record.
    • Pivot - Data to Names - This produces the output and is easy once we have the record id.


    Add a Record Index:

    ConfigureFields:

    out1 += in1
out1.RecordIndex = long

recordIndex = 0

# We need a record index so we know what record each field belongs to.


    ProcessRecords:

    out1 += in1

# Every time we see a _DateTimeStamp field, assume we are starting a new record
if in1.Key == "_DateTimeStamp":
    recordIndex += 1

out1.RecordIndex = recordIndex


    Pivot - Data to Names
    Note that under Optional it was necessary to set NameSet to Union because not all of the output records have all of the field names supplied.



    ------------------------------
    Ernest Jones
    Precisely Software Inc.
    PEARL RIVER NY
    ------------------------------

    Original Message


  • 6.  RE: Data Transformation

    Posted 01-29-2023 10:44
    Appreciates the helps, Ernest...

    Thanks,

    ------------------------------
    Tevita Fainga
    Tonga Communications Corporation
    Fasi-moe-Afi
    ------------------------------

    Original Message