10 Replies Latest reply: Mar 18, 2015 7:26 AM by Giovanni Scalzo

# Multi If Statements

I have the following expression:

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,

But it's incorrect.

If I check the data obtained are also different from the data provided prefixed FVCON_ID_CONTAT = 5.
So i think that the condition FVCON_ID_CONTAT = 5 is not checked.

How can I risolver the problem?

• ###### Re: Multi If Statements

Hi Giovanni,

For nested if use this syntax:

if(Condition, do this,if(condition, do this,else do this))

Regards

KC

• ###### Re: Multi If Statements

I Jyothish, I would apply the syntax with my expression but I can't write correctly ...

• ###### Re: Multi If Statements

Hi

try this,

if(FVCON_ID_CONTAT=5, 'chiuse',

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(DFVCON_TS_ESTR,'YYYYMMDD'),4)),'YYYYMMDD') or (FVCON_ID_CONTAT=5 and FVCON_DATA_FI=0)),'aperte','chiuse')) as SelettorePraticheAperteChiuse,

• ###### Re: Multi If Statements

Hi Harshal, it's wrong to write if(FVCON_ID_CONTAT=5, 'chiuse', because in some case it's possible that the field FVCON_ID_CONTAT is aperte.

• ###### Re: Multi If Statements

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:

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

• ###### Re: Multi If Statements

Hi,

try below

if(FVCON_ID_CONTAT=5,

if(

num(date#(FVCON_DATA_FI,'YYYYMMDD')) = Num(Date#(FVCON_TS_ESTR,'YYYYMMDD')) AND

FVCON_DATA_FI=0,'aperte','chiuse'

),

'chiuse' ) as SelettorePraticheAperteChiuse

Dont play too much with Date() and Date#(), It make confusion. Keep it as simple as you can

and one more advice always better if you compare number instead of date

Regards

• ###### Re: Multi If Statements

It would be easier to debug if you use a preceding Load to do this. Like

If( .... ) as SelettorePraticheAperteChiuse;

date#(trim(right((FVCON_DATA_FI),2) & '/' & mid((FVCON_DATA_FI),5,2) & '/' & Left((FVCON_DATA_FI),4)),'YYYYMMDD') as FVCON_Date1,

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') as FVCON_Date2

and then use the FVCON_Date1 and FVCON_Date2 in your If(...) function.

HIC

• ###### Re: Multi If Statements

Hi Enric, thanks for the answer but I dont' understand what I write in the

If( .... ) as SelettorePraticheAperteChiuse;

• ###### Re: Multi If Statements

I think that you are getting confused with date syntax .....

In principle you are using the correct method of your IF .... statements but I would redo the part that checks the month ...

num(month(FVCON_TS_ESTR)) .... will give you the number for the month and this can be used to compare against you field coming in .... You can use a similar approach to the days and years. This will give you the exact numbering that you want when comparing to your FVCON_DATA_FI field.

Hope this helps