Return first record per part only.

[#1]

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

[johnw · Accepted solution]

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'

[#3]

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]

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

[#5]

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.