topic Re: extract and group data from a column using subfiled in QlikView

extract and group data from a column using subfiled

0li5a3a — Wed, 05 Jul 2017 07:21:38 GMT

Hi all,

I need to extract the data from a column please find below my example:

tab1:

id,

description

from test.qvd

My column @description is like this:

description:

aaa,

bbb,

aabb | aabc | adaca| No data test of test,

aacc | cccsa | cssa| No Data test ,

cccc,

aabb | aabc | adaca| aaa

I would like to extract the data and to achieve something like this:

description:

aaa,

bbb,

No Data ( this mean all the No data to be transformed in No Data)

others

I managed to extract almost what I need but I didn't managed to transform all the No data into No Data.

I used this:

if(WildMatch(trim(subfield(SubField(description,'|',-1),'(',1)),

if(description='No data','No Data','No Data'),'No*','aaa**','bbb*'),trim(subfield(SubField(description,'|',-1),'(',1)),'Other') as Category

Could someone please advise me?

Thanks is advance!

Re: extract and group data from a column using subfiled

tresB — Wed, 05 Jul 2017 07:36:35 GMT

Try like:

if(trim(SubField(Description, '|',-1))='No data', 'No Data',

If(Wildmatch(trim(SubField(Description, '|',-1)),'aaa*','bbb*', 'No Data'),trim(SubField(Description, '|',-1)),'Others')) as Category

Edit: Corrected

Re: extract and group data from a column using subfiled

0li5a3a — Wed, 05 Jul 2017 09:03:08 GMT

I tried this:

if(trim(SubField(Description, '|',-1))='No data*', 'No Data',

If(Wildmatch(trim(SubField(Description, '|',-1)),'aaa*','bbb*', 'No Data*'),trim(SubField(Description, '|',-1)),'Others')) as Category

But I have now this:

aaa,

bbb,

No Data test,

No data test of test,

Others

Thanks

Re: extract and group data from a column using subfiled

Anonymous — Wed, 05 Jul 2017 11:07:14 GMT

Hi Constantin

Try using 'like' instead of '=' in your if statement condition.

Kind regards

MultiView

Re: extract and group data from a column using subfiled

sunny_talwar — Wed, 05 Jul 2017 11:12:15 GMT

What is others here? I mean what exactly is included in others.... Also, is cccc part of the output or is it part of No Data?

Re: extract and group data from a column using subfiled

tresB — Wed, 05 Jul 2017 11:35:43 GMT

Well, you have changed your input and desired output now. Try like:

if(WildMatch(trim(SubField(Description, '|',-1)),'*No data*','*No Data*'),'No Data',

If(Wildmatch(trim(SubField(Description, '|',-1)),'aaa*','bbb*', '*No Data*'),trim(SubField(Description, '|',-1)),

'Others')) as Category

Re: extract and group data from a column using subfiled

0li5a3a — Wed, 05 Jul 2017 12:21:50 GMT

my data is like:

description:

aaa,

bbb,

aabb | aabc | adaca| No data test of test,

aacc | cccsa | cssa| No Data test ,

cccc,

aabb | aabc | adaca| aaa

And I want to have this:

aaa,

bbb,

No Data,

Others (all the rest of the columns)

I used Yaseen advise and now all sorted.

I suppose to write like below:

if(trim(SubField(description, '|',-1)) LIKE 'No data*' OR trim(SubField(description, '|',-1)) LIKE 'No Data*', 'No Data',

if(WildMatch(trim(subfield(SubField(description,'|',-1),'(',1)),'aaa*','bbb*'),

trim(subfield(SubField(description,'|',-1),'(',1)),'Other')) as Category

Thank you all for your help:)

Re: extract and group data from a column using subfiled

0li5a3a — Wed, 05 Jul 2017 12:23:55 GMT

Your answer is good too. Thanks for our help:)