extract and group data from a column using subfiled

[0li5a3a]

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!

[tresesco]

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]

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

[Anonymous · Accepted solution]

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]

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]

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]

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