5 Replies Latest reply: Feb 21, 2017 1:19 AM by Petter Skjolden RSS

    Insert Complex SQL Query on Data Load

    Kevin Somosera

      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!

        • Re: Insert Complex SQL Query on Data Load
          Celambarasan Adhimulam

          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";

          • Re: Insert Complex SQL Query on Data Load
            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;

            • Re: Insert Complex SQL Query on Data Load
              Ryan Quiambao

              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

              • Re: Insert Complex SQL Query on Data Load
                Petter Skjolden

                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.