Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to set a new dimension in script based on string search

Hi All,

I cant find any hint to the below, please could you help?

FACTS:

- I have .csv files that I load to QV

- those files have the same structure, different data

- among the dimensions loaded, there is one called "Transaction text". It contains a string expression (mainly letters)

PROBLEM:

1. I need to separate certain type of the transactions from all the rest (one transaction = one record or row in .csv file)

2. All of those that I need to separate contain "rebill" word in the "Transaction Text" string. None of those that I don't need to separate contains this word

3. My idea was to create a new dimension in the script, containing "YES" in case of all records having "rebill" in the "Transaction text" sting, and "NO" for all the rest

How to write a proper function in the script, doing the job?

Thanks a lot for your help

Regards

Robert

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Actually substringcount returns a number and any number other than zero is evaluated as True. So the >0 isn't really necessary. But making it explicit isn't a bad idea. I'm just a lazy dog

Try the wildmatch function instead. It does case-insensitive matching.

if(wildmatch([Transaction text],'*rebill*'),'Yes','No') as IsRebill


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

3. My idea was to create a new dimension in the script, containing "YES" in case of all records having "rebill" in the "Transaction text" sting, and "NO" for all the rest

Sounds like a good plan. Something like this should work:

if(substringcount([Transaction text],'rebill'),'Yes', 'No') as IsRebill


talk is cheap, supply exceeds demand
Not applicable
Author

thanks Gysbert,

I ammended your formula a little bit:

if(substringcount([Transaction text]>0,'rebill'),'Yes', 'No') as IsRebill

a question - in Transaction text the rebill part can have different capital letters / small letters combinations. Is it a way to avoid enumerating all of them?

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Actually substringcount returns a number and any number other than zero is evaluated as True. So the >0 isn't really necessary. But making it explicit isn't a bad idea. I'm just a lazy dog

Try the wildmatch function instead. It does case-insensitive matching.

if(wildmatch([Transaction text],'*rebill*'),'Yes','No') as IsRebill


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks a lot Gysbert for your help.

I dont know why, but without This >0 the script did not work. Anyway the formuła jakieś its job now...