Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi Constantin
Try using 'like' instead of '=' in your if statement condition.
Kind regards
MultiView
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
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
Hi Constantin
Try using 'like' instead of '=' in your if statement condition.
Kind regards
MultiView
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?
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
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:)
Your answer is good too. Thanks for our help:)