SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';
SET NullValue='<mancante>';
// ODBC CONNECT TO [AS 400 UAT FINPIEMONTE] (XUserId is MFcAGDVIfKVCEKBK, XPassword is BcYTODVIfKVCEKFE);
ODBC CONNECT TO [XXXXXXXXXXXXXXX] (XUserId is YYYYYYYYYYYYY, XPassword is ZZZZZZZZZZZZ);
set vPath='C:\ProgramData\QlikTech\Documents\QVD_PROD\';
set vDatiOn='no';
set vReportOn='sì';
SelettorePraticheAperteChiuse:
LOAD
upper(FVCON_IDFONDO & '-' &
FVCON_NUM_DOM_AS400 & '-' &
FVCON_TIPOL_AGEVOL & '-' &
FVCON_PROGR_WF & '-' &
FVCON_PROGR_TRANCHE) as ChiaveID,
date (date#(FVCON_DATA_FI,'YYYYMMDD'),'DD-MM-YYYY') as [DataFine],
if
(FVCON_ID_CONTAT=5,
if(
(date#(trim(right((FVCON_DATA_FI),2) & '/' & mid((FVCON_DATA_FI),5,2) & '/' & Left((FVCON_DATA_FI),4)),'YYYYMMDD') =
date#(trim(right(Date(FVCON_TS_ESTR,'YYYYMMDD'),2) & '/' & mid(Date(FVCON_TS_ESTR,'YYYYMMDD'),5,2) & '/' & Left(Date(FVCON_TS_ESTR,'YYYYMMDD'),4)),'YYYYMMDD')
or (FVCON_ID_CONTAT=5 and FVCON_DATA_FI=0)
),'aperte','chiuse'
),'chiuse') as SelettorePraticheAperteChiuse,
if(
(FVCON_ID_CONTAT=5 and
date#(trim(right((FVCON_DATA_FI),2) & '/' & mid((FVCON_DATA_FI),5,2) & '/' & Left((FVCON_DATA_FI),4)),'YYYYMMDD') <>
date#(trim(right(Date(FVCON_TS_ESTR,'YYYYMMDD'),2) & '/' & mid(Date(FVCON_TS_ESTR,'YYYYMMDD'),5,2) & '/' & Left(Date(FVCON_TS_ESTR,'YYYYMMDD'),4)),'YYYYMMDD')
)
,date(right(trim(FVCON_DATA_FI),2) & '/' & mid(trim(FVCON_DATA_FI),5,2) & '/' & left(trim(FVCON_DATA_FI),4))) as PraticheChiuseDataFine,
if(
(FVCON_ID_CONTAT=5 and
date#(trim(right((FVCON_DATA_FI),2) & '/' & mid((FVCON_DATA_FI),5,2) & '/' & Left((FVCON_DATA_FI),4)),'YYYYMMDD') <>
date#(trim(right(Date(FVCON_TS_ESTR,'YYYYMMDD'),2) & '/' & mid(Date(FVCON_TS_ESTR,'YYYYMMDD'),5,2) & '/' & Left(Date(FVCON_TS_ESTR,'YYYYMMDD'),4)),'YYYYMMDD')
)
,left(trim(FVCON_DATA_FI),4)) as PraticheChiuseAnnoFine,
if(
(FVCON_ID_CONTAT=5 and
date#(trim(right((FVCON_DATA_FI),2) & '/' & mid((FVCON_DATA_FI),5,2) & '/' & Left((FVCON_DATA_FI),4)),'YYYYMMDD') <>
date#(trim(right(Date(FVCON_TS_ESTR,'YYYYMMDD'),2) & '/' & mid(Date(FVCON_TS_ESTR,'YYYYMMDD'),5,2) & '/' & Left(Date(FVCON_TS_ESTR,'YYYYMMDD'),4)),'YYYYMMDD')
)
,month(date(right(trim(FVCON_DATA_FI),2) & '/' & mid(trim(FVCON_DATA_FI),5,2) & '/' & left(trim(FVCON_DATA_FI),4)))) as PraticheChiuseMeseFine,
if(
(FVCON_ID_CONTAT=5 and
date#(trim(right((FVCON_DATA_FI),2) & '/' & mid((FVCON_DATA_FI),5,2) & '/' & Left((FVCON_DATA_FI),4)),'YYYYMMDD') <>
date#(trim(right(Date(FVCON_TS_ESTR,'YYYYMMDD'),2) & '/' & mid(Date(FVCON_TS_ESTR,'YYYYMMDD'),5,2) & '/' & Left(Date(FVCON_TS_ESTR,'YYYYMMDD'),4)),'YYYYMMDD')
)
,day(date(right(trim(FVCON_DATA_FI),2) & '/' & mid(trim(FVCON_DATA_FI),5,2) & '/' & left(trim(FVCON_DATA_FI),4)))) as PraticheChiuseGiornoFine,
if(FVCON_ID_CONTAT=5 and FVCON_GIORNI=0,'valori a zero','altri valori') as SelettoreValoriAZero
