topic Re: Load Script - if statement with string concatenation in Qlik Learning Discussions

Load Script - if statement with string concatenation

Sun, 05 Oct 2014 13:36:43 GMT

Hi all,

I am having problems to have string concatenation in an if statement in a load script SQL statement.

Example:

SQL

Select IF(Month(`TxDate`) < 8, 'Season ' + Year(`TxDate`), 'Season ' + (Year(`TxDate`)+1)) AS Season

Can anyone explain what I am doing wrong?

Re: Load Script - if statement with string concatenation

antoniotiman — Sun, 05 Oct 2014 14:42:40 GMT

Hi Colin,

If() not is sintax SQL, is sintax QV.

You use

case
When ... then

else ..

end

as Season.

But Month Sintax depending on Your DB.

You can use Preceding Load :

Load *,

If(Month(...),........) as Season;

SQL Select *

from Table;

Regards,

Antonio

Re: Load Script - if statement with string concatenation

Sun, 05 Oct 2014 15:07:11 GMT

Hi Antonio,

Thanks for your reply.

However the If statement is not the issue as it is working fine. It is the concatenation which is working not as expected.

This expression:

('Season ' & (Year(`TxDate`)-1) & '-' & (Year(`TxDate`))

is resulting in 0.

I am sure that Year works fine as I tried it as a separate expression. I am afraid that & is being interpreted as a logical AND expression.

Btw - I am using MySQL but this is not the issue.

Thanks,

Colin

Re: Load Script - if statement with string concatenation

antoniotiman — Sun, 05 Oct 2014 15:12:59 GMT

Colin,

Ok, MySql has If() Sintax.

Tray to remove ' in Year('TxDate') -> Year(TxDate)

Re: Load Script - if statement with string concatenation

Anonymous — Sun, 05 Oct 2014 19:10:26 GMT

Just to be sure, try replacing the &'-'& with &'_'& or some other separator.

Re: Load Script - if statement with string concatenation

Mon, 06 Oct 2014 21:45:04 GMT

I solved this issue with a preceding load as follows:

LOAD *,

IF(Month(`TxDate`) < 8, 'Season ' + Year(`TxDate`), 'Season ' + (Year(`TxDate`)+1)) AS Season;

SQL

Select TxDate

From ...