Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I want to display as like below screen shot way who have two values like with pre-fix value and without pre-fix value as a another table or qvd.
Am attaching file and data, Could you please help me on this.
Advance Thanks,
Munna
How about this?
RawData:
LOAD CHAN_ID,
PROD_CD,
[Product Number],
LOCATOR,
LOCAL_CD,
LOCAL_DESCRP
FROM [.\MLE.xls](biff, embedded labels, table is Sheet1$);
LEFT JOIN (RawData)
LOAD PROD_CD, COUNT(DISTINCT [Product Number]) AS Occurrences
RESIDENT RawData
GROUP BY PROD_CD;
FinalTable:
NOCONCATENATE LOAD * RESIDENT RawData WHERE Occurrences > 1;
DROP Table RawData;
STORE FinalTable INTO [.\MultipleProductNumbers.qvd] (qvd);
See attached document for an example. Modify to suit your needs.
Peter
Not entirely sure but try this:
TextBetween( [Product Number],'-','') as ProductNumber,
Or using subfield():
LOAD
FieldWithPrefix,
subfield(FieldWithPrefix,'-',1) as Prefix,
subfield(FieldWithPrefix,'-',2) as FieldWithoutPrefix,
Hi Munna,
So you want 1 qvd with prefix values and 1 with non prefix values?
If my guess is right for having two tables with and without prefix product number, create a flag and use it as below.
Product:
LOAD CHAN_ID,
PROD_CD,
[Product Number],
Wildmatch([Product Number],'*-*') as Prefixflag,
LOCATOR,
LOCAL_CD,
LOCAL_DESCRP
FROM
MLE.xls
(biff, embedded labels, table is Sheet1$);
NoConcatenate
ProductsWithPrefix:
LOAD *
Resident Product
Where Prefixflag=1;
QUALIFY *;
NoConcatenate
//ProductsWithOutPrefix:
B:
LOAD *
Resident Product
Where Prefixflag=0;
DROP Table Product;
Hi,
try in script
Temp:
Load
....
from ....;
Table:
Concatenate
Load
Replace([Product Number],'20-','') as [Product Number]
Resident Temp;
Regards,
Antonio
No i want to see combination of both values in one qvd.
Regards
munna
I think the solution is to do as Antonio said.
It took me a while to discover your exact requirement, but I think I may have a solution for you.
See the attached document. Please correct me if I'm wrong.
Best,
Peter
MLE:
LOAD CHAN_ID,
PROD_CD,
[Product Number],
LOCATOR,
LOCAL_CD,
LOCAL_DESCRP
FROM
C:\Users\Downloads\MLE.xls
(biff, embedded labels, table is [Sheet1$]);
NEW:
load
CHAN_ID,
PROD_CD,
[Product Number],
LOCATOR,
if (count(DISTINCT [Product Number]) > 1, concat(DISTINCT [Product Number], ', ')) as test
Resident MLE group by CHAN_ID,
PROD_CD, LOCATOR,
[Product Number];
drop Table MLE;
STORE NEW into ' path ';