topic Re: Multi If Statements in QlikView

Multi If Statements

Wed, 18 Mar 2015 10:14:30 GMT

I have the following expression:

(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

jyothish8807 — Wed, 18 Mar 2015 10:22:02 GMT

Hi Giovanni,

For nested if use this syntax:

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

Regards

Re: Multi If Statements

Wed, 18 Mar 2015 10:25:59 GMT

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

robert_mika — Wed, 18 Mar 2015 10:26:40 GMT

Attached your data or file

Re: Multi If Statements

PrashantSangle — Wed, 18 Mar 2015 10:27:06 GMT

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

hic — Wed, 18 Mar 2015 10:28:07 GMT

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

Load *,

If( .... ) as SelettorePraticheAperteChiuse;

Load

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.

See more on Preceding Load

HIC

Re: Multi If Statements

Roop — Wed, 18 Mar 2015 10:28:59 GMT

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

Re: Multi If Statements

Wed, 18 Mar 2015 10:36:16 GMT

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

If( .... ) as SelettorePraticheAperteChiuse;

Re: Multi If Statements

Wed, 18 Mar 2015 10:38:23 GMT

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

Wed, 18 Mar 2015 11:17:16 GMT

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

Re: Multi If Statements

Wed, 18 Mar 2015 11:26:19 GMT

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],

(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