4 Replies Latest reply: Dec 29, 2014 8:55 AM by matteo orighi RSS

    How to add a RowNo field in a Full Join?

    matteo orighi

      Hi, and thanks in advance

      I've a Full Join wich have a null join in the second field: Category.

       

      MyJoinedTable:

      LOAD *, RowNo() as IdA

      INLINE [

      ProductId, Category, NoteA

        55,        X,      Big

        55,        Y,      Small

      ];

      JOIN(MyJoinedTable)

      LOAD *, RowNo() as IdB

      INLINE [

      ProductId, Category, NoteB

        55,        X,      Dog

        55,        Z,      Cat

      ]

      ;

      /* --- Output ---

        IdA,    IdB,    ProductId, Category, NoteA,    NoteB

         1,      1,       55,        X,      Big,      Dog

         2,     <NULL>,   55,        Y,      Small,    <NULL>

        <NULL>,  2,       55,        Z,      <NULL>,   Cat

      */


      The question is: how to add a RowNo() function to have a new field Id (see the Final Output)?

      The only way is to reload the MyJoinedTable into a new table?


      /* --- Final Output (new field Id) ---

      Id,   IdA,     IdB,     ProductId, Category,  NoteA,    NoteB

      1,    1,       1,        55,        X,       Big,      Dog

      2,    2,      <NULL>,    55,        Y,       Small,    <NULL>

      3,   <NULL>,   2,        55,        Z,       <NULL>,   Cat

      */