Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
..............
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
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
..............