topic Re: Substring search question in QlikView

Substring search question

alexis — Wed, 15 Mar 2017 17:06:35 GMT

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

Re: Substring search question

sunny_talwar — Wed, 15 Mar 2017 17:12:28 GMT

May be use MapSubString() function here may be

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/MappingFunctions/MapSubstring.htm

Re: Substring search question

maxgro — Wed, 15 Mar 2017 19:21:24 GMT

// 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;

Re: Substring search question

alexis — Wed, 15 Mar 2017 22:45:39 GMT

Spot-on Massimo - thanks!

Re: Substring search question

alexis — Wed, 15 Mar 2017 22:47:32 GMT

Thanks Sunny and thanks for replying

Check out Massimo's answer below - that is what I was looking for....

Regards

Alexis

Re: Substring search question

sunny_talwar — Wed, 15 Mar 2017 23:38:51 GMT

Awesome