topic Re: right join problem in QlikView

right join problem

ekuijper — Mon, 08 Aug 2016 22:51:07 GMT

Hello all,

I have a problem with a selection in my script.

My original table looks like this:

ID1	ID2	VALUE
1	10	4
1	10	7
2	18
3	33	67
3	33	5

I would like to find the lowest value for every ID2 and also show the corresponding ID1, so I made the following script:

TABLE1:
load * INLINE [
ID1, ID2, VALUE,
1, 10, 4
1, 10, 7
2, 18,
3, 33, 67
3, 33, 5
];

RIGHT JOIN(TABLE1)
LOAD
ID2,
MIN(VALUE) AS VALUE
RESIDENT TABLE1 GROUP BY ID2;

The result is:

This is not as desired, ID1 is missing in the line where there is no value.

Can someone please tell me why ID1 is missing (I expected it to be 2) and how I can solve this?

thanks,

Edwin

Re: right join problem

Anonymous — Tue, 09 Aug 2016 07:44:02 GMT

Hi Edwin, group by ID1 and ID2, regards!

Re: right join problem

avinashelite — Tue, 09 Aug 2016 07:54:03 GMT

I think because of the null value your facing this issue, try to populate the 0 in null value places and check again that should work

Re: right join problem

maxgro — Tue, 09 Aug 2016 07:55:03 GMT

another solution could be

TABLE1:

load * INLINE [

ID1, ID2, VALUE,

1, 10, 4

1, 10, 7

2, 18,

3, 33, 67

3, 33, 5

];

T2:

NoConcatenate

load

ID1, ID2, VALUE

Resident TABLE1

Where ID1 <> Peek('ID1')

order by ID1, ID2, VALUE;

drop Table TABLE1;

Re: right join problem

tamilarasu — Tue, 09 Aug 2016 07:56:52 GMT

Hi Edwin,

TABLE1:
load * INLINE [
ID1, ID2, VALUE,
1, 10, 4
1, 10, 7
2, 18,
3, 12, 2
3, 12, 67
3, 33, 5
];

Final:
NoConcatenate
LOAD ID1,
ID2,
FirstSortedValue(VALUE,VALUE) AS VALUE
RESIDENT TABLE1 GROUP BY ID1, ID2;

DROP Table TABLE1;

Re: right join problem

ekuijper — Tue, 09 Aug 2016 08:33:00 GMT

Hi Tamil,

Thanks, this works great

Edwin

Re: right join problem

ekuijper — Tue, 09 Aug 2016 08:33:59 GMT

Hi Massimo,

Thanks for this solution, I have two working options now.

Edwin