SQL/Qlikview

[#1]

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;

[agomes1971]

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

[maxgro · Accepted solution]

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

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