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;
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] ;
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.
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.
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);
