Search code examples
qlikviewqliksenseqlik-expression

Script not working with STARTS WITH function in Qlik Sense

I'm trying to load some tables using the script, in order to create new tables according to an IF condition.

My script is the following:

LOAD Pippo
if(color = 'green', 'ok',
if(color = 'yellow' and text <> 'No control needed', 'check',
if(color = 'red' and text <> 'Control now', 'check', 'ok'))) as Pippo1

In the line with the color = yellow, I want to catch different cases because there are many text occurrences starting with 'No control needed' (for example No control needed (for 1 week), No control needed (for 2 weeks)), and I want to select them all.

I tried using: text<>'No control needed'*, but it did not work.

Any suggestions?

Solution

  • Index() function can be used in this case.

    Index() will search for string-in-a-string and if not found will return 0 else will return the position on which the searched string is found.

    In your case this can be expressed as:

    Index(text, 'No control needed') = 0

    and the full expression:

    LOAD 
Pippo,
if(color = 'green', 'ok',
if(color = 'yellow' and Index(text, 'No control needed') = 0, 'check',
if(color = 'red' and text <> 'Control now', 'check', 'ok'))) as Pippo1