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.
ekuijper
New 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
MVP
MVP

Re: right join problem

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

6 Replies
manucamon
Valued Contributor III

Re: right join problem

Hi Edwin, group by ID1 and ID2, regards!

Re: right join problem

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

MVP
MVP

Re: right join problem

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

Re: right join problem

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
New Contributor II

Re: right join problem

Hi Tamil,

Thanks, this works great

Edwin

ekuijper
New Contributor II

Re: right join problem

Hi Massimo,

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

Edwin

Community Browser