Forum Discussion

Raph's avatar
Raph
New Contributor II
5 years ago
Solved

How can you sort the Input Schema fields after dragging onto mapper?

I have a quite large excel file (>150 columns) and need to sort the mapped fields precisely for target destination. I use File Reader > Excel Parser > Mapper…

At the moment the fields run A, B,C…BX,BY…GZ. However after dragging the Input Schema fields onto the Mapping Table they are re-ordered A, AA, AB …GY,GZ… X,Y,Z. I need the target schema to be ordered exactly how they are done in the original input. How can this be done?

I tried Sort Snap and Sort Path as $ , $A , just various combinations to no success

Any suggestions on how to avoid ‘manually’ having to set the Fields in the default order. Assume that the default order is mandatory.

mapper
  • del's avatar
    del
    4 years ago

    @Charles,
    Like Patrick, I was also thinking Velocity (VTL) as a solution, but as I tried to solution your original post, the Workday namespace convention in the property names adds some complexity. I don’t know if Velocity beats the null-safe mapper for your particular use case, but it can be good info for your tool belt in the future.

    The kind of ugly solution I came up with (in a one-liner) is:

    [
    {
       "output" : #set ($pd = $['wd:Project_Data']) #set ($ed = $pd['wd:End_Date']) #if (${ed}) ${ed} #else null #end
    }
]

    I’m not well versed in VTL, so there’s likely something a little cleaner than the above.

6 Replies

    • Raph's avatar
      Raph
      New Contributor II
      5 years ago
      Copy LinkReport

      I tried this suggestion. I am sorry I don’t understand what you meant. The mapper generates an error. The snaps used for test were as follows: Filreader > Parser > Mapper (.mapValues((value,key)=>value) > Excel Formatter > File Writer.

      The columns run from A…GM

      Can you clarify a little please?

      • igormicev's avatar
        igormicev
        Contributor
        5 years ago
        Copy LinkReport

        Hi @Raph,

        $.mapValues((value,key)=>value)). I don’t know why i can’t see the sign $. in front of mapValues. Did you maybe use .mapValues((value,key)=>value)) without $ ? You can take a look at the attached pipeline “Original order for columns_2020_10_24.slp” above and execute it. Then just make similarly in your pipeline.
        You can also share your pipeline.

        /Igor

  • del's avatar
    del
    Contributor III
    5 years ago
    Copy LinkReport

    @Raph, I assume you want to do some transformation to the data, or otherwise, I don’t think you need the mapper at all; just push the data as-is to the target, without the mapper.

    If it is the case that you need the mapper and want to maintain the key name order in the output data (and don’t necessarily care about the schema view), you may get by with just checking the checkbox in your mapper. It won’t re-order the schema view in the UI, but you can still manipulate some or all of the data and the output to the target should return to original order.

    Otherwise, if you absolutely need the schema view in the UI to represent the original order, attached below is a sample pipeline with an optional hack to help.

    The Pad Key Names mapper prefixes the key names with spaces, keeping them all the same fixed length, based on the length of the rightmost column key name. Since spaces are first in the schema sort order, and all your key names should be upper case, the Transform Values mapper schema should keep the same key order in its input schema, while allowing you to manipulate the data for the target. The Trim Key Names mapper reverts the key names back to their original names, without spaces, while maintaining the key order.

    Community.8623_2020_10_25.slp (8.1 KB)

    • Raph's avatar
      Raph
      New Contributor II
      5 years ago
      Copy LinkReport

      The pass through solution was effective. It is not shown in the GUI as you suggest however the final output is ordered identically to the input data.

      I will explore the other solution and comment. Pressed for time at the moment. Thank you.