Script using something like STARTS WITH function

[SerSwagster]

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?

[Gui_Approbato]

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]

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]

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]

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 · Accepted solution]

// 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*'