Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
0li5a3a
Creator III
Creator III

extract and group data from a column using subfiled

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!

1 Solution

Accepted Solutions
multiview
Creator II
Creator II

Hi Constantin

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

Kind regards

MultiView

View solution in original post

7 Replies
tresesco
MVP
MVP

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

0li5a3a
Creator III
Creator III
Author

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



multiview
Creator II
Creator II

Hi Constantin

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

Kind regards

MultiView

sunny_talwar

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?

tresesco
MVP
MVP

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

0li5a3a
Creator III
Creator III
Author

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:)

0li5a3a
Creator III
Creator III
Author

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