topic Re: Sql "LIKE" Issue in a Sql select Load Statement In Qlik Sense? in Connectivity & Data Prep

Sql "LIKE" Issue in a Sql select Load Statement In Qlik Sense?

Fri, 19 Aug 2016 12:40:29 GMT

Hello,

I am trying to load columns from our database to Qlik sense. Some of the columns will be derived columns.

For eg : In my sql statement if I have something similar to this

Query: CASE WHEN Status LIKE 'Rejected*' THEN 'Rejected' ELSE 'Accepted' END AS CurrentStatus

Output: This column will show "Rejected" for every line regardless of the Status .

If I change my query to

Query: CASE WHEN Status = 'Rejected' THEN 'Rejected' ELSE 'Accepted' END AS CurrentStatus

Output: It derives the right value for each line.

I have tried using IF Statement instead of CASE and that also doesnt seem to work.

Is there anything that I am missing? Any help/guidance would be appreciated!

Thanks!!!

Re: Sql "LIKE" Issue in a Sql select Load Statement In Qlik Sense?

hic — Fri, 19 Aug 2016 13:38:17 GMT

This is SQL syntax, so it is not evaluated by Qlik Sense. Instead it is evaluated by the ODBC driver / OLEDB provider. So, what's possible or not depends on which ODBC driver you use...

A work-around is to use a preceding Load:

Load *, If(Status='Rejected', Status, 'Accepted') as Status2;

SQL SELECT ... FROM ... ;

Then the Load is evaluated by Qlik Sense.

HIC

Re: Sql "LIKE" Issue in a Sql select Load Statement In Qlik Sense?

Fri, 19 Aug 2016 15:12:15 GMT

Hi Henric,

Thanks for the response...

Turns out all I had to do was change the wildcard from '*' to '%' and it worked fine.

I understand now that '%' works for sql select statements and if you need to use a wildcard from a csv file, you need to use '*'.

Thanks again so much for taking the time to look into this.

Regards