SQL/Qlikview

Wed, 09 Jul 2014 14:07:14 GMT

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

agomes1971 — Wed, 09 Jul 2014 16:46:34 GMT

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

maxgro — Wed, 09 Jul 2014 18:13:13 GMT

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

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

topic SQL/Qlikview in QlikView

SQL/Qlikview

Re: SQL/Qlikview

Re: SQL/Qlikview