topic Return first record per part only. in QlikView

Return first record per part only.

Wed, 30 Nov 2011 19:50:02 GMT

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

Re: Return first record per part only.

johnw — Wed, 30 Nov 2011 22:26:17 GMT

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'

Return first record per part only.

Thu, 01 Dec 2011 12:15:45 GMT

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

Load

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

WhereKeep='Y';

Select

From ...;

Load

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

Select

From ... Where Keep='Y';

Return first record per part only.

sridhar240784 — Thu, 01 Dec 2011 13:34:40 GMT

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

Return first record per part only.

Thu, 01 Dec 2011 14:17:03 GMT

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.