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

Return first record per part only.

I have a Part table. Our system allows you to attach a drawing to a part record, and this is stores in another table. You are able to attach more than one drawing to each part, hence creating a one to many link.

How can I tell qlikview to only return the first record per part? IE if one part has 2 drawings I only want to have the first found drawing to be returned.

Edit: Would doing something like this work? I would apply the same formula to all fields so if the previous record is the same part (IE this is the second attachment) it would return null values?

if(peek(partnum)=partnum,null(),partnum) as part

1 Solution

Accepted Solutions
MVP
MVP

Re: Return first record per part only.

Same basic idea, but I might do something like:

if(peek(partnum)<>partnum,'Y') as Keep

And then use a preceding load with:

WHERE Keep = 'Y'

4 Replies
MVP
MVP

Re: Return first record per part only.

Same basic idea, but I might do something like:

if(peek(partnum)<>partnum,'Y') as Keep

And then use a preceding load with:

WHERE Keep = 'Y'

Not applicable

Return first record per part only.

would the where be in the load, or the select section?

Load

*,

if(peek(partnum)<>partnum,'Y') as Keep

WhereKeep='Y';

Select

*

From ...;

or

Load

*,

if(peek(partnum)<>partnum,'Y') as Keep;

Select

*

From ... Where Keep='Y';

sridhar240784
Contributor III

Return first record per part only.

Hi,

Your code should be as follows ( as suggested by John )

Load  *  where Keep='Y';

Load

*,

if(peek(partnum)<>partnum,'Y') as Keep;

Select

*

From ...;


-Sridhar

Not applicable

Return first record per part only.

Ok I finally got it to work using your help. It was not loading the data according to the part so I had to use a resident table with Order by. Seems to be working correctly now.

Community Browser