Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
SerSwagster
Creator
Creator

Script using something like STARTS WITH function

Hi,

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?

1 Solution

Accepted Solutions
JGMDataAnalysis
Creator II
Creator II

 

// Case Sensitive
If((color = 'yellow' AND Left(text, 17) <> 'No control needed')
    OR (color = 'red' AND text <> 'Control now'), 'check', 'ok'
) AS Pippo1

 

 

// Case Insensitive
If((color = 'yellow' AND Lower(Left(text, 17)) <> 'no control needed')
    OR (color = 'red' AND text <> 'Control now'), 'check', 'ok'
) AS Pippo1

 

 

View solution in original post

6 Replies
Gui_Approbato
Creator III
Creator III

You can use the Wildmatch function.

It ignores the case type and you can add the not instead of <>. Example:

 

if( not wildmatch(ColumnName, 'No *') , ColumnName )  as Column

 

JGMDataAnalysis
Creator II
Creator II

A slightly more compact option...

If((color = 'yellow' AND Left(text, 17) = 'No control needed')
    OR (color = 'red' AND text <> 'Control now'), 'check', 'ok'
) AS Pippo1
SerSwagster
Creator
Creator
Author

Yeah, it works (although i wanted AND NOT Left(text, ..)!!!

One more thing: is LEFT operator case insensitive? Because I need to avoid both strings written in these ways:

- 'No control needed...'

- 'No Control needed...'

- 'No control Needed...?

etc.

Or
MVP
MVP

Left() is neither case sensitive nor case insensitive - it just pulls the relevant part of the string.

You can always use Upper() or Lower() on both sides of the comparison if necessary.

JGMDataAnalysis
Creator II
Creator II

 

// Case Sensitive
If((color = 'yellow' AND Left(text, 17) <> 'No control needed')
    OR (color = 'red' AND text <> 'Control now'), 'check', 'ok'
) AS Pippo1

 

 

// Case Insensitive
If((color = 'yellow' AND Lower(Left(text, 17)) <> 'no control needed')
    OR (color = 'red' AND text <> 'Control now'), 'check', 'ok'
) AS Pippo1

 

 

Saravanan_Desingh

Try this,

Not text Like 'No control needed*'