topic Help Flagging & eliminating duplicates or Simplifying my Loads for for Three fields (SQL in ERP works but cant get same results in Qlikview) in QlikView

Help Flagging & eliminating duplicates or Simplifying my Loads for for Three fields (SQL in ERP works but cant get same results in Qlikview)

Thu, 21 Apr 2011 01:31:29 GMT

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

Help Flagging & eliminating duplicates or Simplifying my Loads for for Three fields (SQL in ERP works but cant get same results in Qlikview)

Thu, 21 Apr 2011 04:18:40 GMT

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

Help Flagging & eliminating duplicates or Simplifying my Loads for for Three fields (SQL in ERP works but cant get same results in Qlikview)

Thu, 21 Apr 2011 16:52:55 GMT

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,