Insert Complex SQL Query on Data Load

[ksomosera10]

Hi,

I'm new at Qlik Sense and I just want to know how can I insert SQL "CASE" statement on Data Load script. Here is what i want to do:

LOAD "Month";

SQL SELECT

CASE

  WHEN A.Month = '01-Jan' THEN '1'

  WHEN A.Month = '02-Feb' THEN '2'

  WHEN A.Month = '03-Mar' THEN '3'

  WHEN A.Month = '04-Apr' THEN '4'

  WHEN A.Month = '05-May' THEN '5'

  WHEN A.Month = '06-June' THEN '6'

  WHEN A.Month = '07-July' THEN '7'

  WHEN A.Month = '08-Aug' THEN '8'

  WHEN A.Month = '09-Sept' THEN '9'

  WHEN A.Month = '10-Oct' THEN '10'

  WHEN A.Month = '11-Nov' THEN '11'

  WHEN A.Month = '12-Dec' THEN '12'

  END AS [Month]

FROM DBDATA.dbo."tbl_Simulation_Data";

Hope you could help me out. Thanks!

[CELAMBARASAN]

This would have worked right? Whats your error here?

For Month, you can go with simple expressions

LOAD Month(A) as MONTH;

SQL SELECT

  A //assuming that this is the date field

FROM DBDATA.dbo."tbl_Simulation_Data";

[ksomosera10]

I'm getting this message every time I load my script.

[shubham_singh]

You didn't define the table alias A, which you are using in your query(A.Month). Maybe this should work.

LOAD "Month";

SQL SELECT

CASE

  WHEN A.Month = '01-Jan' THEN '1'

  WHEN A.Month = '02-Feb' THEN '2'

  WHEN A.Month = '03-Mar' THEN '3'

  WHEN A.Month = '04-Apr' THEN '4'

  WHEN A.Month = '05-May' THEN '5'

  WHEN A.Month = '06-June' THEN '6'

  WHEN A.Month = '07-July' THEN '7'

  WHEN A.Month = '08-Aug' THEN '8'

  WHEN A.Month = '09-Sept' THEN '9'

  WHEN A.Month = '10-Oct' THEN '10'

  WHEN A.Month = '11-Nov' THEN '11'

  WHEN A.Month = '12-Dec' THEN '12'

  END AS [Month]

FROM DBDATA.dbo."tbl_Simulation_Data" as A;

[#5]

Hi Kevin,

My suggestion is extract (SQL SELECT) data from your source as it is, don't add condition at extraction level, do the condition on succeeding levels (TRANSFORMATION LEVEL), or you can even do the conditions on the Presentation Level / Report Level.

Ryan

[petter]

Be aware that any SQL will be sent directly to the ODBC-driver and interpreted there and on the back-end database. Any syntax and how you write date-literals have to stick to what the ODBC-driver and the back-end database demands. This is not governed or controlled by Qlik Sense.

Have you tried running the SQL in a SQL-query tool that works with your database directly first to see if there is any syntax errors or other problems with the SELECT statement? If not I suggest that you do that to weed out any problems there first. After you have a valid and running SELECT you can copy it to the load script.

I don't know if your SELECT was just a simple example or actual code that you intend to use... Be aware of that most SQL databases have a Month()-function that could replace the CASE WHEN construct you made which is much more effective and simple.