How do i script to select only the first record of the first dimension when other two dimensions can make record unique

[#1]

i have a file with 200k records and multiple fields (see attached item master QVD) and want to load only three fields below

[Part number]

[Item Status]

[Commodity Code]

My desired result is, part number sorted ascending, status sorted ascending and commodity code

but i only want to select the first record for each part number.

My output will only have one part number, the first status code associated with that part number and the commodity code associated with that part number.

below is desired result

Part Number	Item Status	Commodity Code
6551-246	1	093

However there can be many status combinations and commodity code combinations for One Part number. Although there can be many combinations for one part number i am only concerend with the selecting the first record of a part number once each of the above three fields have been sorted ascending.

I have tried to load my QVD, sort the above three fields ascending and then isolate the resident table but need some help as i am still very new to Qlikveiw. Previously i dont think that i clarified my question.

Attached is my QVW and my Item Master QVD

the below is are the current results that i am getting

Part Number	Item Status	Commodity Code
6551-246	1	093
6551-246	4	OBP
6551-246	4	093

or Part Number Item Status Commodity Code 6551-246 1 093 6551-246 4 OBP

as you can see my current results would be fine if i wanted all combinations for each part but i want to select only the first record regardless of all possible combinations, if the three fileds are sorted ascending then by selecting the first record for each part, i will achieve my desired output

[disqr_rm]

Try:

LOAD
[Part number],
[Item Status],
[Commodity Code]
FROM myQvd.qvd (qvd)
WHERE Not Exists ([Part number]);

[#3]

Item Master QVD

[#4]

i tried your suggestion and am prompted field not found [part number].

wont this just omit the filed [Part Number] from the load?

[#5]

Part Number	Item Status	Commodity Code
6551-246	1	093
6551-246	4	OBP
6551-246	4	093

I dont think first value would work because i would like to see the above data in the below format

Part Number	Item Status	Commodity Code
6551-246	1	093

One Part number, with only one status code which should be the first record of data when sorted ascending and then the commodity code would be what is listed by default.

the first table in this post shows All unique combination for each part however i am only concerned with pickup up only the first record once part number and status have been sorted ascending

[disqr_rm]

in QV Fields arecase sensitive. Try this:

LOAD

[Part Number],[Item Status],[Commodity Code]
FROM
C:\Temp\ItemMaster.qvd
(qvd)
WHERE NOT Exists ([Part Number]);

[#7]

Rakesh thank you for your input however when i script the below

LOAD

[Part Number],[Item Status],[Commodity Code]

FROM
C:\Temp\ItemMaster.qvd
(qvd)
WHERE NOT Exists ([Part Number]);

I do only get one part number, one status code and one commodity code which is what i wanted

However

I'll need to Sort the [Part number] ascending, [Item status] ascending to get the right status and commodity code and then run your recommendation

If would be easier if i could sort the data as it is loaded from the QVD but my understanding is that i can only do an ORDER BY ASC for a field from a resident table not from a QVD load directly

It seems like i will have to load the QVD

then do

ORDER BY [Part Number] ASC, [Item Status] ASC, [Commodity Code] ASC

on the resident table

then some how add your WHERE NOT Exists ([Part Number]);

but i am having trouble pulling all of these steps together

[#8]

I attempted the below but am still getting the wrong record,

for part number 6551-246 i should get status 1 and commodity code 093

instead with the below i am getting

part number 6551-246

status 4

commodity code OBP

which shows that my records aren't being sorted ascending before the NOT Exist is added

Itemtemp:

LOAD

[Part Number],

[Item Status],

[Commodity Code]

FROM $(Stage2QVDPath1)\ItemMaster.qvd(qvd);

INNER JOIN

LOAD

[Part Number],

[Item Status],

[Commodity Code]

RESIDENT Itemtemp

ORDER BY [Part Number]ASC,[Item Status]ASC,[Commodity Code]ASC;

STORE Itemtemp INTO $(Stage2QVDPath1)\Itemtemp.qvd;

DROP TABLE Itemtemp;

Itemtemp2:

LOAD

[Part Number],

[Item Status],

[Commodity Code]

FROM $(Stage2QVDPath1)\Itemtemp.qvd(qvd)

WHERE NOT Exists ([Part Number]);

[#9]

Also tried and Item status still will not sort ascending,

Itemtemp:

LOAD

[Part Number],

[Item Status],

[Commodity Code],

recno() as Recno

FROM $(Stage2QVDPath1)\ItemMaster.qvd(qvd);

INNER JOIN (Itemtemp)

LOAD

min(Recno) as Recno,

[Part Number],

[Item Status],

[Commodity Code]

RESIDENT Itemtemp

GROUP BY [Part Number],[Item Status],[Commodity Code]

ORDER BY [Part Number]ASC,[Item Status]ASC,[Commodity Code]ASC;

STORE Itemtemp INTO $(Stage2QVDPath1)\Itemtemp1.qvd;

DROP TABLE Itemtemp;

Itemtemp3:

LOAD

[Part Number],

[Item Status],

[Commodity Code],

recno() as Recno

FROM $(Stage2QVDPath1)\Itemtemp1.qvd(qvd)

WHERE NOT Exists ([Part Number]);

INNER JOIN (Itemtemp3)

LOAD

min(Recno) as Recno,

[Part Number],

[Item Status],

[Commodity Code]

RESIDENT Itemtemp3

GROUP BY [Part Number],[Item Status],[Commodity Code]

ORDER BY [Part Number]ASC,[Item Status]ASC,[Commodity Code]ASC;

[#10]

Hi Joseph,

Kindly refer the attached sample application

may be it will help

Regards,

Sampath Kumar . G