Discussion Board for collaboration on QlikView Scripting.
I have a problem with a selection in my script.
My original table looks like this:
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, 41, 10, 72, 18, 3, 33, 673, 33, 5];
RIGHT JOIN(TABLE1)LOADID2,MIN(VALUE) AS VALUERESIDENT 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?
Go to Solution.
another solution could be
load * INLINE [
ID1, ID2, VALUE,
1, 10, 4
1, 10, 7
3, 33, 67
3, 33, 5
ID1, ID2, VALUE
Where ID1 <> Peek('ID1')
order by ID1, ID2, VALUE;
drop Table TABLE1;
Hi Edwin, group by ID1 and ID2, regards!
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
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;
Thanks, this works great
Thanks for this solution, I have two working options now.