12 Replies Latest reply: Aug 25, 2016 1:56 AM by Ram Krishna RSS

    Sql query to Qlikview Script..

    Nikitha Jain

      Hi Friends,

       

       

      I have below sample SQL Query which I need to convert into equivalent Qlikview (AQL) Script. Please guide me accordingly.

       

      SELECT student_clasification_SOURCE  student_source

            ,ei.e_item_id

            ,EI.E_ITEM_DATE

            ,EI.E_TYPE Expnd_type

            ,(SELECT   e_category

                     FROM   table12

                    WHERE   ex_type = ei.ex_type)

                     EXPND_CATEGORY

            ,DECODE (EI.SLI_FUNCTION,

                         'USG',

                         'Usages',

                      

                         'PJ',

                         'Miscellaneous ') Type_Class

            ,p.segment1          project_number

            ,x.E_GROUP       Expenditure_batch

            ,x.CR_DATE     Expnd_batch_creation_date

            ,(SELECT   p.full_name

                     FROM   Ptable11 p

                    WHERE   p.student_id = x.incurred_by_student_id

                            AND EI.Actual_date BETWEEN p.effective_start_date

                                                             AND  p.effective_end_date)

                     EMPLOYEE_NAME

             ,(SELECT   DECODE (p.current_employee_flag,

                                    'Y', p.student_number,

                                    NVL (p.npw_number, p.student_number))

                     FROM   table11 p

                    WHERE   p.student_id = x.incurred_by_student_id

                            AND ei.Actual_date BETWEEN p.effective_start_date

                                                             AND  p.effective_end_date)

                     student_NUMBER

             ,x.incurred_by_student_id Student_id

      FROM   Table1 P,

             Table2 T,

             Table3 I,

             TAble4 X,

             Table5 PT,

             Table6 TR,

             TAble7 O1,

             TAble8 J

      WHERE T.field7 = P.field7

            AND I.field6_ID = P.field6_ID

            AND P.filed4type = PT.field4type

            AND P.field3_ID = PT.field3_ID

            AND I.field2_ID = T.field2_ID

            AND I.field1_ID = X.field1_ID

          

         AND NVL (I.OVERRIDE_TO_ORGANIZATION_ID,

                     X.IN_BY_ORGANIZATION_ID) = O1.ORGANIZATION_ID

          

         AND I.sub_ID = J.sub_ID(+)

            AND I.classification_SOURCE = TR.classification_SOURCE(+)

            AND tr.student_clasification_SOURCE is null

      --order by x.in_by_Student_id      ;

       

       

      Regards,

      Niki

        • Re: Sql query to Qlikview Script..
          Jonathan Penuliar

          Hi Nikitha,

           

          you can load data off database using SQL:

           

          SQL ‒ QlikView

          • Re: Sql query to Qlikview Script..
            André Gomes

            Hi,

             

            a advice create a table with data an then import it to QlikView it will work faster.

             

            Lije this:

             

            create view put_a_name as

            SELECT student_clasification_SOURCE  student_source

                  ,ei.e_item_id

                  ,EI.E_ITEM_DATE

                  ,EI.E_TYPE Expnd_type

                  ,(SELECT   e_category

                           FROM   table12

                          WHERE   ex_type = ei.ex_type)

                           EXPND_CATEGORY

                  ,DECODE (EI.SLI_FUNCTION,

                               'USG',

                               'Usages',

                           

                               'PJ',

                               'Miscellaneous ') Type_Class

                  ,p.segment1          project_number

                  ,x.E_GROUP       Expenditure_batch

                  ,x.CR_DATE     Expnd_batch_creation_date

                  ,(SELECT   p.full_name

                           FROM   Ptable11 p

                          WHERE   p.student_id = x.incurred_by_student_id

                                  AND EI.Actual_date BETWEEN p.effective_start_date

                                                                   AND  p.effective_end_date)

                           EMPLOYEE_NAME

                   ,(SELECT   DECODE (p.current_employee_flag,

                                          'Y', p.student_number,

                                          NVL (p.npw_number, p.student_number))

                           FROM   table11 p

                          WHERE   p.student_id = x.incurred_by_student_id

                                  AND ei.Actual_date BETWEEN p.effective_start_date

                                                                   AND  p.effective_end_date)

                           student_NUMBER

                   ,x.incurred_by_student_id Student_id

            FROM   Table1 P,

                   Table2 T,

                   Table3 I,

                   TAble4 X,

                   Table5 PT,

                   Table6 TR,

                   TAble7 O1,

                   TAble8 J

            WHERE T.field7 = P.field7

                  AND I.field6_ID = P.field6_ID

                  AND P.filed4type = PT.field4type

                  AND P.field3_ID = PT.field3_ID

                  AND I.field2_ID = T.field2_ID

                  AND I.field1_ID = X.field1_ID

               

               AND NVL (I.OVERRIDE_TO_ORGANIZATION_ID,

                           X.IN_BY_ORGANIZATION_ID) = O1.ORGANIZATION_ID

               

               AND I.sub_ID = J.sub_ID(+)

                  AND I.classification_SOURCE = TR.classification_SOURCE(+)

                  AND tr.student_clasification_SOURCE is null

            --order by x.in_by_Student_id      ;

             

             

            and then do:

             

             

            Select * into new_table  from  put_a_name

             

            HTH

             

            André Gomes

            • Re: Sql query to Qlikview Script..
              Manideep Hv

              Hi Niki,

               

              Create a view using the above query and then you pull that query to qlikview.

               

              EG:

               

              CREATE VIEW <View_Name>

              AS

               

              SELECT student_clasification_SOURCE  student_source

                    ,ei.e_item_id

                    ,EI.E_ITEM_DATE

                    ,EI.E_TYPE Expnd_type

                    ,(SELECT   e_category

                             FROM   table12

                            WHERE   ex_type = ei.ex_type)

                             EXPND_CATEGORY

                    ,DECODE (EI.SLI_FUNCTION,

                                 'USG',

                                 'Usages',

                             

                                 'PJ',

                                 'Miscellaneous ') Type_Class

                    ,p.segment1          project_number

                    ,x.E_GROUP       Expenditure_batch

                    ,x.CR_DATE     Expnd_batch_creation_date

                    ,(SELECT   p.full_name

                             FROM   Ptable11 p

                            WHERE   p.student_id = x.incurred_by_student_id

                                    AND EI.Actual_date BETWEEN p.effective_start_date

                                                                     AND  p.effective_end_date)

                             EMPLOYEE_NAME

                     ,(SELECT   DECODE (p.current_employee_flag,

                                            'Y', p.student_number,

                                            NVL (p.npw_number, p.student_number))

                             FROM   table11 p

                            WHERE   p.student_id = x.incurred_by_student_id

                                    AND ei.Actual_date BETWEEN p.effective_start_date

                                                                     AND  p.effective_end_date)

                             student_NUMBER

                     ,x.incurred_by_student_id Student_id

              FROM   Table1 P,

                     Table2 T,

                     Table3 I,

                     TAble4 X,

                     Table5 PT,

                     Table6 TR,

                     TAble7 O1,

                     TAble8 J

              WHERE T.field7 = P.field7

                    AND I.field6_ID = P.field6_ID

                    AND P.filed4type = PT.field4type

                    AND P.field3_ID = PT.field3_ID

                    AND I.field2_ID = T.field2_ID

                    AND I.field1_ID = X.field1_ID

                 

                 AND NVL (I.OVERRIDE_TO_ORGANIZATION_ID,

                             X.IN_BY_ORGANIZATION_ID) = O1.ORGANIZATION_ID

                 

                 AND I.sub_ID = J.sub_ID(+)

                    AND I.classification_SOURCE = TR.classification_SOURCE(+)

                    AND tr.student_clasification_SOURCE is null

              --order by x.in_by_Student_id      ;

               

              ***********************************

              In qlikview : LOAD *;

              SQL Select * from <View_Name>

               

              Regards,

              Manideep

              • Re: Sql query to Qlikview Script..
                Anil Babu Samineni

                You are using

                 

                SELECT student_clasification_SOURCE  student_source

                      ,ei.e_item_id

                      ,EI.E_ITEM_DATE

                      ,EI.E_TYPE Expnd_type

                      ,(SELECT   e_category

                               FROM   table12

                 

                In that, Where do you use this ei, EI

                 

                FROM   Table1 P,

                       Table2 T,

                       Table3 I,

                       TAble4 X,

                       Table5 PT,

                       Table6 TR,

                       TAble7 O1,

                       TAble8 J

                • Re: Sql query to Qlikview Script..
                  Ram Krishna

                  Hi Nikitha,

                   

                  Use the expression like below to convert SQL select statement to QlikView-

                  SQL SELECT "CUST_ID",

                      "ORDER_ID",

                      "SALE_DATE"

                  FROM Test.dbo.CUSTMERORDERS;

                   

                  Thanks,

                  Ram Krishna