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.
