Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
ekuijper
Contributor II
Contributor II

right join problem

Hello all,

I have a problem with a selection in my script.

My original table looks like this:

ID1ID2VALUE
1104
1107
218
33367
3335

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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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;


1.png

View solution in original post

6 Replies
manucamon
Specialist III
Specialist III

Hi Edwin, group by ID1 and ID2, regards!

avinashelite

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

maxgro
MVP
MVP

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;


1.png

View solution in original post

tamilarasu
Champion
Champion

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;

ekuijper
Contributor II
Contributor II
Author

Hi Tamil,

Thanks, this works great

Edwin

ekuijper
Contributor II
Contributor II
Author

Hi Massimo,

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

Edwin