Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am still on the lower end of the learning curve with Qlikview and one of the only person trying to excel and learn Qlikview, my company implemented Qlikview a few months ago and i have been trying to learn as much as can.
I have three fields: [Part Number], [Item Status] and [Commodity Code
I can run a SQL view in our ERP system below
SELECT impn, imsts, imccod
FROM sun400mfg/fkitmstr
WHERE
GROUP BY
HAVING
UNION
ORDER BY impn ASC, imsts ASC, imccod ASC
and set
Number of unique keys . . . 1
Join type . . . . . . . . . *PARTOUT
and i get exactly what i need one part number sorted ascending, one status code sorted ascending and one commodity code sorted ascending.
Currently the below is my issue, I have also attached a QVW.
I have spent over 8 hrs trying to script the above in Qlikview and can't get the same results. Instead i get the attached which is the same as the below
Part Number | Item Status | Commodity Code |
---|---|---|
6551-246 | 1 | 093 |
6551-246 | 4 | OBP |
6551-246 | 4 | 093 |
The ideal script would give me the first record 6551-246 for a [Part number], Status 1 since it's sorted ascending and [Commodity Code] 093. I have over 200k records in my file, the above is just a one part example to illustrate my issue.
Any help would be greatly appreciated. I've researched and I dont think that i explained my issue clearly in my previous post
Best Regards
Hi,
I`m not sure i have understood your question, i assume that you want a below table as your output
Part Number | Item Status | Commodity Code |
---|---|---|
6551-246 | 1 | 093 |
6551-246 | 4 | OBP |
If that is the case instead of using a simple Join use INNER JOIN, QV default join is OUTER JOIN.
Your Code should be as follows
Itemtemp:
LOAD
[Part Number],
[Item Status],
[Commodity Code],
recno() as Recno
FROM $(Stage2QVDPath1)\ItemMaster.qvd(qvd);
INNER JOIN (Itemtemp)
LOAD DISTINCT
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;
If this doesnt solve your purpose, share you ItemMaster.qvd and also your requirements clearly
- Sridhar
Sridhar thank you for adding your input.
If part number is sorted ascending ,item status ascending and commodity code ascending the "Only select the first record" for each part number, regardless if there are multiple item status & commodity code combinations.
Part Number | Item Status | Commodity Code |
---|---|---|
6551-246 | 1 | 093 |
My desired output would be to sort part number ascending, item status ascending and commodity code ascending,
then only select the first record for each part number as shown above,