2 Replies Latest reply: Jul 9, 2014 2:13 PM by Massimo Grossi RSS

    SQL/Qlikview

      Hi! i have to transform a SQL query in Qlikview and i'm working on the follow table, but there is a part of the original script in sQL that i'm not able to translate in Qlikview language. The part of the script is the following:

       

      (case when INVOICE_COMPANY_ID in (SELECT   DISTINCT company_id

                                                            FROM   sites_society

                                                             WHERE   SYSDATE BETWEEN start_date AND end_date AND company_id != '--')

      then DECODE (INVOICE_COMPANY_ID, 'SI', 'RS', INVOICE_COMPANY_ID)

      else 'RS' end) SITE_COMPANY_ID,

       

      then there is all the script of the table that i've already converted. So some one can explain me how i can convert in qlikview the previous part ??

       

      QVD1:

       

      LOAD  INVOICE_COMPANY_ID,

                 INVOICE_YEAR,

                 INVOICE_OFFICE,

                 INVOICE_ID,

                 INVOICE_DATE,

                 IF(isnull(INVOICE_EXPIRE_DATE), INVOICE_DATE, INVOICE_EXPIRE_DATE) as INVOICE_EXPIRE_DATE,

      // NVL (inv.INVOICE_EXPIRE_DATE, inv.INVOICE_DATE) INVOICE_EXPIRE_DATE,

                 INVOICE_PAYMENT_DATE,

                 INVOICE_TYPE,

                 ACCOUNT_ID,

                 ACCOUNT_STATUS,

                 COMPANY_ID,

                        

                 //    (case when INVOICE_COMPANY_ID in (SELECT   DISTINCT company_id

                 //                                                            FROM   sites_society

                 //                                                            WHERE   SYSDATE BETWEEN start_date AND end_date AND company_id != '--')

                 //      then DECODE (INVOICE_COMPANY_ID, 'SI', 'RS', INVOICE_COMPANY_ID)

                 //      else 'RS'

                 //      end) SITE_COMPANY_ID,

                                      

                 SITE_ID,

                 CURRENCY_RATE,

                 CURRENCY_CODE,

                 CONTRACT_COMPANY_ID,

                 CONTRACT_YEAR,

                 CONTRACT_ID,

                 SUBSECTOR,

                 SUM (AMOUNT_EURO) AS AMOUNT_EURO,

                 SUM (AMOUNT)  AS AMOUNT,

                 SUM (TO_BE_PAYED) AS TO_BE_PAYED,

                 SUM(if(ACCOUNT_STATUS='F',COUNTRY_CURRENCY_TO_BE_PAYED,                      (IF(ACCOUNT_STATUS='E',TO_BE_PAYED,COUNTRY_CURRENCY_TO_BE_PAYED)))) AS LOCAL_CURRENCY_DUE,         

                             // SUM ( DECODE(account_status,'F', COUNTRY_CURRENCY_TO_BE_PAYED, 'E', TO_BE_PAYED, COUNTRY_CURRENCY_TO_BE_PAYED) )  LOCAL_CURRENCY_DUE,

      if(ACCOUNT_STATUS='F',COUNTRY_CURRENCY_CODE,IF(ACCOUNT_STATUS='E',CURRENCY_CODE,COUNTRY_CURRENCY_CODE)))AS LOCAL_CURRENCY_CODE             

      //  DECODE(account_status, 'F', COUNTRY_CURRENCY_CODE, 'E', CURRENCY_CODE, COUNTRY_CURRENCY_CODE)   LOCAL_CURRENCY_CODE

               //--SUM ( COUNTRY_CURRENCY_TO_BE_PAYED) LOCAL_CURRENCY_DUE,

               //--COUNTRY_CURRENCY_CODE   LOCAL_CURRENCY_CODE

          FROM [$(vQVDPath)INVOICES.qvd] (qvd)// FROM   invoices

        where INVOICE_TYPE='FT' or INVOICE_TYPE='NC' or INVOICE_TYPE='MM' or INVOICE_TYPE='ZZ'

      GROUP BY   INVOICE_COMPANY_ID,

                 INVOICE_YEAR,

                 INVOICE_OFFICE,

                 INVOICE_ID,

                 INVOICE_DATE,

                 IF(isnull(INVOICE_EXPIRE_DATE), INVOICE_DATE, INVOICE_EXPIRE_DATE),

      //NVL (INVOICE_EXPIRE_DATE, INVOICE_DATE),

                 INVOICE_PAYMENT_DATE,

                 INVOICE_TYPE,

                 ACCOUNT_ID,

                 ACCOUNT_STATUS,

                 COMPANY_ID,

                 SITE_ID,

                 CURRENCY_RATE,

                 CURRENCY_CODE,

                 CONTRACT_COMPANY_ID,

                 CONTRACT_YEAR,

                 CONTRACT_ID,

                 SUBSECTOR,

                 TO_BE_PAYED,

                 COUNTRY_CURRENCY_TO_BE_PAYED,

                 COUNTRY_CURRENCY_CODE;

        • Re: SQL/Qlikview
          André Gomes

          Hi Gabriele,

           

          why don't you load your original script as a view to load all the contents in ine shot?

           

          Kind regards

           

          André Gomes

          • Re: SQL/Qlikview
            Massimo Grossi

            maybe

             

            MapTable:

            mapping load

            company_id as from,

            company_id as to;

            SQL SELECT   DISTINCT company_id

            FROM   sites_society

            WHERE   SYSDATE BETWEEN start_date AND end_date AND company_id != '--';

             

            QVD1:

            load

              somefields,

              ................,

              if(applymap('MapTable', INVOICE_COMPANY_ID, 'not found')<>'not found',

                  // found in sites_society

              if(INVOICE_COMPANY_ID='SI', 'RS', INVOICE_COMPANY_ID),        

                 // not found

              'RS'                                                                

              ) as SITE_COMPANY_ID,

              someotherfields,

               .........

            from

               ..............