topic Re: how to use case statement in query using sqlbase database in QlikView

how to use case statement in query using sqlbase database

mcoolen01 — Fri, 05 May 2017 18:30:09 GMT

Hello,

I am trying to carry out a case statement in the SQL section using a SQLBase database but I cannot find the correct syntax.

Can anybody help me finding the correct syntax. What I want to do is concatenate a zero if the period is smaller than 10.

Example: If the periode is 9, I want to use 09.

I tried:

LOAD

Bookyear,

Period;

SQL Bookyear,

Periode

CASE

WHEN period < 10

THEN (0||periode)

ELSE (period)

END as Period

FROM CROPEN

Thank you for helping me with this issue.

Re: how to use case statement in query using sqlbase database

maxgro — Fri, 05 May 2017 19:22:02 GMT

try

LOAD

Bookyear,

num(Period, '00') as Period

;

SQL Bookyear,

Period

FROM CROPEN

;

Re: how to use case statement in query using sqlbase database

Oleg_Troyansky — Fri, 05 May 2017 19:22:06 GMT

Hi Michel,

I'm not too sure about the exact SQLBase solution, but I could offer a QlikView solution that seems to be a lot more elegant. You can simply format the QlikView number to always carry two digits. Your script would look like this:

LOAD

Bookyear,

NUM(Period, '00') as Period

;

SQL

Bookyear,

Periode as Period

FROM CROPEN

;

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Boston, MA this October!

Re: how to use case statement in query using sqlbase database

mcoolen01 — Wed, 10 May 2017 14:07:33 GMT

Hello Oleg,

Thank you for helping me out, I appreciate that.

The thing is that I need to load (sql) data smaller than e.g. year 2016 period 10.

So if I use (e.g.) 'WHERE period <= 10' in the sql section, I miss periods 11 and 12 for previous years.

That's why I thought to load all years and periods (sql) and than filter the loaded data in the load sector.

But I do not know how to do that.

An other solution is to use a if-then-else in my query, but I cannot find the correct syntax (SQLBase).

Greetz,

Michel

Re: how to use case statement in query using sqlbase database

Oleg_Troyansky — Wed, 10 May 2017 17:54:00 GMT

Hi Michel,

I think you should look up the corresponding SQL syntax that will allow you formatting the Year-Month combinations using the SQL syntax. For example, something like this:

SQL FORMAT() Function

Think along the lines of formatting your data in a certain way, NOT along the lines of using CASE or IF statements to determine if the month is less than 10 - it will be way to slow...

cheers,

Oleg Troyansky