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

Where exists alternative

Hello Friends

I have a situation for which the code is below:

TempTableA:

Load max([Temp ID]) AS [Temp ID],[Orig Temp ID] from TableA.qvd(qvd) group by [Orig Temp ID];

TableA:

Load * from TableA.qvd(qvd) where exists ([Temp ID]) and exists ([Orig Temp ID]);

Drop Table TempTableA:

So basically, what I am trying to do with this piece of code is that for each [Orig Temp ID] I want the corresponding max([Temp ID]) only.

SalesPerson       Orig Temp ID           Temp ID

John Doe                  1                       111

John Doe                  1                      222

John Doe-New          1                       333

So therefore I want only the 3rd row to showup only in my output

So basically, what I am trying to do with this piece of code is that for each [Orig Temp ID] I want the corresponding max([Temp ID]) only.


Is using the wherexists a good option or is there an alternate best way to do?


Note: Temp ID is my primary Key


Thanks

2 Replies

Re: Where exists alternative

Hi Rizwan,

Try this,

Data:
LOAD SalesPerson,
[Orig Temp ID],
[Temp ID]
FROM
TableA.qvd
(
qvd);

Inner Join
Load [Orig Temp ID],
Max( [Temp ID]) as [Temp ID]
Resident Data Group by [Orig Temp ID];

Re: Where exists alternative

Another option:

TableA:

LOAD FirstSortedValue(SalesPerson, -[Temp ID])  as SalesPerson

          [Orig Temp ID],

          Max([Temp ID]) as [Temp ID]

FROM TableA.qvd (qvd)

Group By [Orig Temp ID];

Community Browser