Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
One of the tables that I am reading from the source database has a field that we will call Products.
The Products field will typically contain semicolon separated values, e.g.
DD;EX;CD;FG
... and let's suppose that in it's simplest for the table is loaded as:
LOAD DISTINCT
KeyField
If(not IsNull(Products),
SubFields(Products, ';') As Product
FROM Products.qvd;
This creates multiple records for each distinct KeyField/Product combination.
This approach is not desirable because I want to create a single record for each Keyfield and want to instead create new columns that describe my Products data.
Specifically, taking the DD;PP;EX;PP;CD;FG example, let's suppose that I have the following classifications:
ClassA: DD,PP,DP,PD
ClassB: CD
ClassC: All the rest - I wish to ignore these (e.g. EX and FG above can be ignored)
I wish to change my load statement above where I will end up with a result that will comprise 4 fields:
KeyField: (as read from table)
Products: (as read from table)
ClassAProductCode: (to contain the code (see list above) if found in Products - there can ever only be one of these values at any one time, DD in this example)
ClassBFound: (to contain a Y(es) if the Products field has a CD value in the string, or N(o) if it does not)
My logic tells me that there is a better way to do this than the following:
LOAD DISTINCT
KeyField,
Products,
If(IsNull(Products),'none',
if(index(Products, 'DD')>0, 'DD',
if(index(Products, 'PP')>0, 'PP',
if(index(Products, 'DP')>0, 'DP',
if(index(Products, 'PD')>0, 'PD')),'none')) As ClassAProductCode,
If(IsNull(Products), 'N',
if(index(Products, 'CD')>0, 'Y','N')) As ClassBFound
FROM Products.qvd;
Any suggestions how this can be achieved more efficiently would be very much appreciated.
Thanking you in advance
Alexis
// test data, replace with your qvd
tmp:
load * Inline [
KeyField,Product
1, DD;PP;EX;PP;CD;FG
2, DD;EX;CD;FG
3, EX;CD;FG
4, EX
5, EX;PD
6, EX;PD;DP
];
tmp2:
NoConcatenate load
*,
Pick(WildMatch(Product, '*DD*', '*PP*', '*DP*', '*PD*', '*'), 'DD', 'PP', 'DP', 'PD', 'none') as ClassAProductCode,
Pick(WildMatch(Product, '*CD*', '*'), 'Y', 'N') as ClassBProductCode
Resident tmp;
DROP Table tmp;
May be use MapSubString() function here may be
// test data, replace with your qvd
tmp:
load * Inline [
KeyField,Product
1, DD;PP;EX;PP;CD;FG
2, DD;EX;CD;FG
3, EX;CD;FG
4, EX
5, EX;PD
6, EX;PD;DP
];
tmp2:
NoConcatenate load
*,
Pick(WildMatch(Product, '*DD*', '*PP*', '*DP*', '*PD*', '*'), 'DD', 'PP', 'DP', 'PD', 'none') as ClassAProductCode,
Pick(WildMatch(Product, '*CD*', '*'), 'Y', 'N') as ClassBProductCode
Resident tmp;
DROP Table tmp;
Spot-on Massimo - thanks!
Thanks Sunny and thanks for replying
Check out Massimo's answer below - that is what I was looking for....
Regards
Alexis
Awesome