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;
