Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ksomosera10
Contributor

Insert Complex SQL Query on Data Load

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!

Tags (1)
5 Replies

Re: Insert Complex SQL Query on Data Load

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
Contributor

Re: Insert Complex SQL Query on Data Load

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

Capture.JPG

shubham_singh
Contributor II

Re: Insert Complex SQL Query on Data Load

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;

Not applicable

Re: Insert Complex SQL Query on Data Load

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

MVP
MVP

Re: Insert Complex SQL Query on Data Load

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.