topic Re: Problem with nested if-statements in QlikView

Problem with nested if-statements

Tue, 17 Mar 2015 15:55:47 GMT

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

(FVCON_DATA_FI=0)),'aperte','chiuse')) as SelettorePraticheAperteChiuse,

The problem is that the value of SelettorePraticheAperteChiuse is only chiuse but I would that have the value aperte and chiuse.

Where is the problem ?

Thanks, Giovanni

Re: Problem with nested if-statements

Anonymous — Tue, 17 Mar 2015 16:15:40 GMT

The problem is more likely with the data than with the nested if, but your expression is way more complex than it has to be, and it's not clear what to return if FVCON_ID_CONTAT<>5 (I added chiuse).

See what I got after cleanup:

if(FVCON_ID_CONTAT=5,
    if(date(date#(FVCON_DATA_FI,'YYYYMMDD')) = date(FVCON_TS_ESTR)
   or FVCON_DATA_FI=0),'aperte','chiuse'),
   'chiuse') as SelettorePraticheAperteChiuse,

Re: Problem with nested if-statements

Tue, 17 Mar 2015 16:31:32 GMT

The solution the you write is wrong.

I obtain only the value chiuse for SelettorePraticheAperteChiuse.

I don't know how can I write the expression ...

Re: Problem with nested if-statements

Anonymous — Tue, 17 Mar 2015 16:38:15 GMT

If its wrong its not a solution . And, I only simplified your expression, to show how to write it in a more clear way. Back to my major point: "The problem is more likely with the data...". If you need a solution, you have to upload your application.

Re: Problem with nested if-statements

Tue, 17 Mar 2015 16:43:30 GMT

I have attached the file. On the line 437 there is the expression ....

Re: Problem with nested if-statements

Anonymous — Tue, 17 Mar 2015 17:05:44 GMT

Giovanni,

Can you reduce the file? To 1 MB or less please?

From your last comment it looks like the expression is in the script, not in a front end object. It make it harder to troubleshoot because I cannot reload, so I have to ask a few questions.

My assumption is that FVCON_DATA_FI and FVCON_TS_ESTR are dates in different formats, and that your want result to be 'aperte' if the dates are equal or FVCON_DATA_FI=0, but only when the FVCON_ID_CONTAT=5.

So, the questions are:

- What is format of FVCON_DATA_FI?

- What is format of FVCON_TS_ESTR?

- What should be the result if FVCON_ID_CONTAT <> 5?

Thanks,

Michael

Re: Problem with nested if-statements

Tue, 17 Mar 2015 18:36:25 GMT

i tried to open you document.. but it is not opening

Re: Problem with nested if-statements

Wed, 18 Mar 2015 09:54:52 GMT

If I use 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_DATA_FI=0

),'aperte','chiuse'),'chiuse') as SelettorePraticheAperteChiuse,

Now I obtain the value aperte and chiuse but there is a problem.

I get the values of aperte/chiuse for FVCON_ID_CONTAT that are different from the condition set FVCON_ID_CONTAT = 5.

What I wrong ?

Re: Problem with nested if-statements

Wed, 18 Mar 2015 12:10:19 GMT

Hi gys, the solution that you write me are wrong ...

Please help me ...I'm new of QlikView and It's dificult to me to resolve this problem.

Thanks a lot.

Re: Problem with nested if-statements

Wed, 18 Mar 2015 13:01:50 GMT

Hi Giovanni

I think you might be using the OR incorrectly and need to change it to read as below for it to work

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

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

=FVCON_DATA_FI=0),'aperte','chiuse'),'chiuse') as SelettorePraticheAperteChiuse

Hope this helps you

Helen

Re: Problem with nested if-statements

Wed, 18 Mar 2015 13:51:35 GMT

Hi Helen, it don't works ...missing a parenthesis and I don't know where ...

Re: Problem with nested if-statements

Wed, 18 Mar 2015 14:03:31 GMT

Hi Giovanni

I have double checked and there is no missing parenthesis, I presumed this was part of a load script, was I correct?

Helen

Re: Problem with nested if-statements

Wed, 18 Mar 2015 14:28:15 GMT

Hi Helen, in your script I removed the condition

=FVCON_DATA_FI=0) ....

The expression is:

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

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

=FVCON_DATA_FI),'aperte','chiuse'),'chiuse') as SelettorePraticheAperteChiuse,

And I have no problem, it works ok but I have another problem.

If I use this field I have the value aperte and chiuse for the same ID, only for some ID, not for ALL.

Why ?

Thanks a lot Helen, if you have some suggest please tell me ...