topic How to load file with 26 fields but only load unique records based on three fields in QlikView

How to load file with 26 fields but only load unique records based on three fields

Fri, 15 Apr 2011 20:37:45 GMT

I have a file on our ERP system named PRTLST that contains a complete part listing with all details for standard costs

i would like to load this entire file and save as a QVD however there are many duplicates

How can i sort three fields acsending (PartNumber, [Commodity Code],STATUS) then script in to only pull in Unique records?

is this possible in Stage one or must this be done in a two stage process?

How to load file with 26 fields but only load unique records based on three fields

spividori — Fri, 15 Apr 2011 21:30:26 GMT

Hi.

I don't understand your problem very well, you've tried to load "distinct" and "order by"?.
You can upload a data to better understand?

Regards.

How to load file with 26 fields but only load unique records based on three fields

rwunderlich — Sun, 17 Apr 2011 06:28:53 GMT

So the three fields have duplicates, but the other 23 fields are different? What rule do you want to use to select from the duplicates?

How to load file with 26 fields but only load unique records based on three fields

Tue, 19 Apr 2011 20:41:07 GMT

I have 23 fields but and i want to sort by [Part Number] ASC, [Commodity Code] ASC and [Status] ASC

the same part number could have multiples status codes etc

so i would like to sort based on the above then only load the first record of each duplicate.

i Tried the below but am getting the message, Garbage after statement when i debug, do i need to the break this up into a 3 step process, first load the three fields, then sort them Ascending then Load distinct from a resident table?

LOAD Distinct

[Part Number],

[Commodity Code],

[Status]

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

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

How to load file with 26 fields but only load unique records based on three fields

rwunderlich — Tue, 19 Apr 2011 22:06:47 GMT

You can only use ORDER BY for a RESIDENT table. You'll need to do a couple steps.

1. Add a Recno field to each row using recno(). You can do this when loading the QVD.

2. Inner join a grouped load of the resident table against the resident table. Something like

INNER JOIN (data)
LOAD
min(Recno ) as Recno , // Select first record from the group sort order
[Part Number], [Commodity Code],[Item Status]
RESIDENT data
GROUP BY [Part Number], [Commodity Code],[Item Status]
ORDER BY [Part Number], [Commodity Code],[Item Status] ;

How to load file with 26 fields but only load unique records based on three fields

Tue, 19 Apr 2011 22:29:16 GMT

Thank you very much for your help, however have a clarification question:

If i want to sort by Part number ascending, [Item Status] ascending and [commodity code] ascending for each record and only want to take the first record of duplicates that way i'll only get unique records, with Part number, Item status and commodity code combinations

How come LOAD DISTINCT wont work since LOAD DISTINCT is supposed to take the first record of duplicates?

i'm going to give your recommendation a shot but was just wondering the above.

How to load file with 26 fields but only load unique records based on three fields

johnw — Tue, 19 Apr 2011 22:45:57 GMT

LOAD DISTINCT doesn't take the first record of duplicates; it loads distinct values of the stated fields. Loading the distinct values of part number, item status and commodity code does you little good. What would you do with that? Join it back to the main table? Nothing would happen and your main table would still have duplicates. Or if you meant as your main load, you could only load those three fields that way. Then how would you get the other fields? Or if you put all the fields in the load distinct, you'll get distinct combinations of all fields, not just the three fields.

How to load file with 26 fields but only load unique records based on three fields

Tue, 19 Apr 2011 23:20:26 GMT

thanks, this helps me to better understand when Load Distinct is used vs above recomendation.

How to load file with 26 fields but only load unique records based on three fields

Wed, 20 Apr 2011 21:03:31 GMT

Rob i tried loading the below

Itemtemp:

LOAD

[Part Number],

IF(TRIM([Item Status])='A' OR TRIM([Item Status])='D',0,TRIM([Item Status])) as [Item Status],

[Commodity Code]

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

Itemtemp2:

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],[Item Status],[Commodity Code];

STORE Itemtemp2 INTO $(QVDDir)\Item2.qvd;

DROP TABLE Itemtemp;

but i keep gettign the below error message

"Field not found - <Recno>

Itemtemp2:"

How to load file with 26 fields but only load unique records based on three fields

johnw — Wed, 20 Apr 2011 21:20:39 GMT


jsphbustos wrote: i keep gettign the below error message
 "Field not found - <Recno>


Rob Wunderlich wrote:1. Add a Recno field to each row using recno(). You can do this when loading the QVD.

In other words:

Itemtemp:
LOAD
[Part Number],
IF(TRIM([Item Status])='A' OR TRIM([Item Status])='D',0,TRIM([Item Status])) as [Item Status],
[Commodity Code],
recno() as Recno
FROM $(Stage2QVDPath1)\ItemMaster.qvd(qvd);

How to load file with 26 fields but only load unique records based on three fields

Wed, 20 Apr 2011 23:05:28 GMT

I tried the below and i am getting unique record combinations.

However one more step would be greatly appreciated.

If a Part number has three different combinations such as Status Codes and Commodity Codes as seen below, how can i script Qlikview to only select the first record. which has been sorted ascending.

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

Itemtemp:

LOAD

[Part Number],

[Item Status],

[Commodity Code],

recno() as Recno

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

Itemtemp2:

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;

How to load file with 26 fields but only load unique records based on three fields

rwunderlich — Thu, 21 Apr 2011 22:06:40 GMT

Joseph,

All three of these threads you opened seem to be on the same question? Am I understanding correctly?

http://community.qlik.com/forums/p/44389/177904.aspx#177904

http://community.qlik.com/forums/p/44641/178126.aspx#178126

http://community.qlik.com/forums/p/44695/178154.aspx#178154

How to load file with 26 fields but only load unique records based on three fields

Thu, 21 Apr 2011 22:19:10 GMT

Yes, i have researched and cant seem to find solution.

and, since i am new it appears that each time i was unable to communicate my exact problem in a clear enough manner to receive a to the point solution or help and i didnt PM anyone because i know that a lot of times it is recommended to post in forums so other can learn too.

I think i got the issue clearly explained in the third thread but can not script the correct solution.

How to load file with 26 fields but only load unique records based on three fields

johnw — Tue, 26 Apr 2011 23:22:54 GMT

So you only want to keep unique records based on ONE field instead of three, but sorted in the order of the three? Perhaps something like this:

Itemtemp:
LOAD
[Part Number]
,[Item Status]
,[Commodity Code]
, your other 23 fields
FROM $(Stage2QVDPath1)\ItemMaster.qvd(qvd);

INNER JOIN (Itemtemp)
LOAD *
RESIDENT ItemTemp
WHERE [Part Number]<>previous([Part Number])
ORDER BY [Part Number], [Item Status], [Commodity Code];