Qlik Community

New to Qlik Sense

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

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
SerSwagster
Contributor III
Contributor III

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
Contributor III
Contributor III
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
Master II
Master II

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

 

 

View solution in original post

Saravanan_Desingh

Try this,

Not text Like 'No control needed*'