Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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;

Tags (1)
1 Solution

Accepted Solutions
maxgro
Not applicable

Re: SQL/Qlikview

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

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

2 Replies
agomes1971
Not applicable

Re: SQL/Qlikview

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
Not applicable

Re: SQL/Qlikview

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

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