Multi If Statements

[#1]

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?

[jyothish8807]

Hi Giovanni,

For nested if use this syntax:

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

Regards

KC

[#3]

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,

[robert_mika]

Attached your data or file

[PrashantSangle]

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

[hic]

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

[Roop]

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

[#8]

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

If( .... ) as SelettorePraticheAperteChiuse;

[#9]

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.

[#10]

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