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

138 Members
Join Us!

I am new to DMX . I want to create a DMX Join on two flat file source. In one file I want to combine two string fields and on resultant field I want to apply substring. After getting substring I want to use that field to apply Join Condition with second file's field.  How Can I do this . Please Help

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

Join Syncsort Community

Email me when people reply –

Replies

  • Hi AK,

    For the scenario you described, a simple use case that comes to mind relates to photo naming conventions. You probably wouldn’t have a use case this trivial, but I’ll use it as an example to explain how I would go about creating a DMX task for your scenario.

    In the first input file (assume it’s delimited by a pipe character “|”), you might have 2 fields, where the first field is the common photo prefix “DSC”, and the second field contains a photo number in each record, as in the following sample:

    DSC|501
    DSC|502
    DSC|503
    and so on.

    Now let’s assume the second input file also has 2 fields, the first one being the photo number, and the second being the date the photo was taken. Again, the fields are delimited by a pipe (“|”):

    501|01/20/2017
    502|01/21/2017
    503|01/22/2017
    and so on.

    First off, make sure you select “Join” at the top of the DMX GUI to specify that you are creating a Join task. Then, specify the left and right side sources as the 2 input files and create the record layouts for each source.  Now, in order to concatenate the 2 fields from the first file, you would click the “Value” button at the top of the GUI, and then, to the expression box, add the photo prefix field from the first file, then press the “||” button to concatenate, and then add the photo number field from the first file. (Note: if you specified the photo number field as having the data type “number” as opposed to “text”, then before adding it to the expression box, wrap it in the ToText() function. You cannot concatenate non-text fields.) 

    The expression box should look like this afterward, if the two original fields had text data types:

    (recordlayout1.PhotoPrefix || recordlayout1.PhotoNum)


    If the PhotoNum field from the first file had a number data type, the expression box would look like this instead:

    (recordlayout1.PhotoPrefix || ToText(recordlayout1.PhotoNum))


    Once you’ve created this new value containing the concatenation of the two fields (let’s call it NewPhotoName), we can move on to extracting a substring. In this example, we can create another value which uses the substring function to extract the photo number from a value that now looks like “DSC501”. Since we know the prefix “DSC” is always 3 characters, we can make a value that says (Substring(NewPhotoName,4)). This means that the resulting value will be a substring of NewPhotoName, from the 4th character to the end of the string. Let’s call this new value NewNameSubstr.

    Now that we have the substring, we can define our join. In this case, the left side value we will join on is our new value, NewNameSubstr, which was obtained from manipulations to fields in the first file, and the right side value we will join on is the PhotoNumber field from the second file. In the join dialog, check “Output matched records” so the output file will only contain records in which the photo number being read from the first file also exists in the second file.

    If we make a reformat for the target file, we can now output the NewPhotoName field (which will look like “DSC501”), and the date the photo was taken.

    Final output would look something like this:

    DSC501|01/20/2017

    Hope this helped!

This reply was deleted.

To access Syncsort Knowledge Base, visit:

My Support