Join this group to access featured content and participate in discussions related to DMX.

120 Members
Join Us!

Combining records into one

I am new to DMX. I need to create a task that reads a flat file of sorted records, compares certain fields among records with matching keys and determines new values for those fields, then writes out a flat file with one record per key with the calculated field values.For example, for records with the same key, I need to determine the correct Exemption-Code. If any Exemption-Code is blank, then the output Exemption-Code should be blank; if any Exemption-Code is different from another, the output should be 'D'; if any Exemption-Code is outside the range of allowed values, the output should be 'B'; otherwise, write the Exemption-Code.Again, I only want to write one record per key, with the correct Exemption-Code as determined above.

You need to be a member of Syncsort Community to add comments!

Join Syncsort Community

Email me when people reply –

Replies

  • Hi Mary,

    You would need two aggregate tasks to perform the operation.

    ===========TASK 1==============

    In the first aggregate you would use the existing sorted key along with the Exemption-Code as group by field. Lets say for example :

    Sorted Key -> Emp-Num & Emp-Sal

    So, Group By Fields should be Emp-Num, Emp-Sal & Exemption-Code.

    In the same task, you would create two named values as  

    EmptyExCode : IfThenElse (Exemption-Code = "", 1, 0)

    IllegalExCode : IfThenElse (Exemption-Code < low or ExCode > high, 1, 0)

    //Here low-high is the range of allowed values. 

    Then, Total these above two named values in the Summary field of Aggregation dialog box. 

    ===========TASK 2============== 

    In second aggregate task, the Sort key is used as the group by field.

    In the summary field, you would create a Count All (to determine if all codes were same), total all new fields created and select an Any on Exemption-Code this time. 

    In this task as well, create a named value as 

    NewExCode : IfThenElse ( sum (emptyexcode)>0,"",IfThenElse ( count ()>1, "D",IfThenElse (sum(IllegalExCode)>0,"B",Any (ExemptionCode))))

    This will give you one record for each unique key value and NewExCode defines the correct Exemption-Code. Hope this helps.

    Please note that the above conditions & values are cited as examples and are not necessarily syntactically correct. Use DMX dialog boxes for appropriate syntax.

    Regards,

    Sushree Mishra

    • Sushree, thank you for your response. I have not just one, but several such fields (that must be compared among all records having the same key to determine the correct field value), so I take it this means I need to do two aggregations for each such field, and a further aggregation to set the final output field values?  Also with this file, there are numeric fields that must be summed for each key depending on certain conditions (e.g. if all records are 'paid' then add all the 'paid' amounts, but if any record is 'due' add only the 'due' amounts). There are still other conditions in regard to which values are retained for the final output record.  I think this project is beyond the scope of what I can reasonably attempt using only DMX. Would you agree?

      • Hi Mary,

        It definitely sounds like a very good fit for DMX, but I would need more details to suggest the complete end-to-end solution. If you could share the detailed requirements along with some sample data, I would recommend the appropriate solution accordingly.

        Regards,

        Sushree Mishra

        • Of course, I can't post the actual specs or any (proprietary) data.
          Records without matching keys are to be written out as-is.
          For records with matching keys, combine them into a single output record:
          Exemption-Code is as described in my original post (except there are actually 8 different values to consider)
          Amount + Amount-Code (there are 5 occurrences per record, each occurrence treated individually): If all Amount-Codes (for an occurrence) are the same, sum all Amounts (for that occurrence); otherwise, if any Amount-Code = '0' then sum only the Amounts where the Amount-Code = '0' and move '0' to the output Amount-Code
          D-Indicator: If any record has 'S' then write 'S'; else if any record has 'D' then write 'D'; else blank
          Fill in all the other fields (there are over 100 fields):
          If all occurrences of Amount-Code are the same, use the record with the lowest Serial-Number that is of S-type = 'R' or 'C'; otherwise use the record with the lowest Serial-Number regardless of S-type.

This reply was deleted.

To access Syncsort Knowledge Base, visit:

My Support