Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to add a RowNo field in a Full Join?

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

*/

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

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

]

;

Load RowNo() As Id,* Resident MyJoinedTable;

Drop Table MyJoinedTable;

View solution in original post

4 Replies
MarcoWedel

What about

Left Join (MyJoinedTable)

LOAD distinct

           ProductId,

           Category,

           AutonumberHash128(ProductId, Category) as Id

Resident MyJoinedTable;

?

Not applicable
Author

but if I had one more row (NoteA=MEDIUM) the Id is not unique

/* --- 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>

2,    3,      <NULL>,    55,        Y,        MEDIUM,   <NULL>

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

*/

anbu1984
Master III
Master III

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

]

;

Load RowNo() As Id,* Resident MyJoinedTable;

Drop Table MyJoinedTable;

Not applicable
Author

Yes, the only way is reload the table.

Thank's to all!