Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community
Im loading from a catalog (*.xls) of products that looks like this
Cod Description
10001 Bottled Pepsi 250m
10001 Bottled Pepsi 250ml blue
10001 Bottled Pepsi 250ml red
10002 Tea Green
10002 Tea Green 1 L
and so on...
I want to load the code but have only the first Description. In plain English eliminate the rest of descriptors for same Cod.
Is there any way to do this so at the end will have only ?
10001 Bottled Pepsi 250m
10002 Tea Green
and so on.
Thank you in advance.
Yours
Mark
LOAD Cod,
Description
FROM YourExcel.xls
WHERE NOT EXISTS(Cod):
Hi,
You can flag rows that you wanted as Y and unwanted as N and filter it while loading.
Hi Marketh,
To Achieve this ,you have to use 'First Sorted Value' function.
let me know if you have any questions.
Also see attached qvw for the code.
cheers.
WHERE NOT EXISTS(Cod)
eliminate all duplicated rows from table
10001 Bottled Pepsi 250m
10002 Tea Green
Hi pratap,
he is not asking to eliminate the duplicates rather he wants the to pull only the 1st description.
you code eliminates duplicates but in the above examples one code has multiple descriptions which are not duplicates.
Thanks
krishna
I think WHERE NOT EXISTS(Cod) should fulfill the requirement, unless field Cod has been loaded in a previous table already.
Then you need something like
LOAD Cod,
Cod as CodCheck,
Description
FROM YourExcel.xls
WHERE NOT EXISTS(CodCheck, Cod):
DROP FIELD CodCheck;
As far as I understand, you are trying to get the simplest description for each product. If so, then you need to use MinString, please see the below screenshot:
And your data model will look like the below:
What if he has not loaded the field Cod in the previous table ?
Then you can go with my very first suggested code:
product:
LOAD * INLINE [
Code,Desc
10001,Bottled Pepsi 250ml
10001,Bottled Pepsi 250ml blue
10001,Bottled Pepsi 250ml red
10002,Tea Green
10002,Tea Green 1 L
10002,Tea Green 2 L
]
WHERE NOT EXISTS(Code);