Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Honored Contributor III

Re: How to add a RowNo field in a Full Join?

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;

4 Replies

Re: How to add a RowNo field in a Full Join?

What about

Left Join (MyJoinedTable)

LOAD distinct

           ProductId,

           Category,

           AutonumberHash128(ProductId, Category) as Id

Resident MyJoinedTable;

?

Not applicable

Re: How to add a RowNo field in a Full Join?

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
Honored Contributor III

Re: How to add a RowNo field in a Full Join?

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

Re: How to add a RowNo field in a Full Join?

Yes, the only way is reload the table.

Thank's to all!

Community Browser