topic Re: Error in Expression :( in QlikView

Error in Expression :(

Mon, 09 Dec 2013 04:58:08 GMT

Hi I am checking the following condition in my expression.

vFromDate and vToDate are my two variables and I am checking the following condition :

1. if ELIG_EFF_DT between vFromDate and vToDate
2. ELIG_TERM_DT between vFromDate and vToDate

3. VFromDate between ELIG_EFF_DT and ELIG_TERM_DT
4. And vToDate between ELIG_EFF_DT and ELIG_TERM_DT

The 1,2 conditions are wrkn properly but getting an error in 3,4.

Is the syntax correct??

Message was edited by: Poornima Narayanan Attached the QVW

Re: Error in Expression :(

Mon, 09 Dec 2013 05:12:35 GMT

Can you please post the sample app

Re: Error in Expression :(

tresB — Mon, 09 Dec 2013 05:16:11 GMT

Might be this is because you are using a variable in place of a field. One more thing; you can directly write like Field={">12<100"} to mean between; '*' would nto be required. Could you post a sample ?

Re: Error in Expression :(

Anonymous — Mon, 09 Dec 2013 05:28:50 GMT

In 3 & 4 , you are getting error as you cannot pass variables , as only field is passed for the comparison.

Regards

Nitin

Re: Error in Expression :(

Mon, 09 Dec 2013 05:37:16 GMT

Replace $(vFromDate) by vFromDate.

similarly

Replace $(vToDate) by vToDate.

Re: Error in Expression :(

CELAMBARASAN — Mon, 09 Dec 2013 05:42:46 GMT

Hi,

Instead of writing 3rd and 4th condition, use * in between the first 2 expressions. like the one below

If(ValueList('Male','Female','Unknown') = 'Male',

(COUNT({ <ELIG_GENDR_CD = {'M'},ELIG_EFF_DT = {">=$(=Date(vFromDate))<=$(=Date(vToDate))"} ,ELIG_AGE={'>=18'}>

* <ELIG_GENDR_CD = {'M'},ELIG_TERM_DT = {">=$(=Date(vFromDate))<=$(=Date(vToDate))"} ,ELIG_AGE={'>=18'}>}

DISTINCT ELIG_PERSON_KEY)))

Re: Error in Expression :(

tresB — Mon, 09 Dec 2013 05:43:42 GMT

Try like:

If(ValueList('Male','Female','Unknown') = 'Male',

(COUNT({ <ELIG_GENDR_CD = {'M'},ELIG_EFF_DT = {">=$(=Date(vFromDate))"}*{"<=$(=Date(vToDate))"} ,ELIG_AGE={'>=18'}>

+ <ELIG_GENDR_CD = {'M'},ELIG_TERM_DT = {">=$(=Date(vFromDate))"}*{"<=$(=Date(vToDate))"} ,ELIG_AGE={'>=18'}>

+ <ELIG_GENDR_CD = {'M'},ELIG_EFF_DT = {">=$(=Date($(vFromDate))) <=$(=Date($(vToDate)))"} ,ELIG_AGE={'>=18'}>

}

DISTINCT ELIG_PERSON_KEY)))

PFA

Re: Error in Expression :(

Mon, 09 Dec 2013 05:45:54 GMT

I want this condition to be checked

1. VFromDate between ELIG_EFF_DT and ELIG_TERM_DT
2. And vToDate between ELIG_EFF_DT and ELIG_TERM_DT

Re: Error in Expression :(

tresB — Mon, 09 Dec 2013 05:53:56 GMT

I am afraid that what you are trying is both logically and technically inappropriate. Field values are usually compared against variable values and not the opposite way. You probably need to reconsider your logic.

Re: Error in Expression :(

Mon, 09 Dec 2013 05:55:24 GMT

Does that solve all my four conditions

if ELIG_EFF_DT between vFromDate and vToDate
ELIG_TERM_DT between vFromDate and vToDate

3. VFromDate between ELIG_EFF_DT and ELIG_TERM_DT

4. And vToDate between ELIG_EFF_DT and ELIG_TERM_DT

Re: Error in Expression :(

CELAMBARASAN — Mon, 09 Dec 2013 05:56:20 GMT

Yes it will solve.

Edit:

Sorry I misunderstood the conditions. Please try with this

If(ValueList('Male','Female','Unknown') = 'Male',

(COUNT({ <ELIG_GENDR_CD = {'M'},ELIG_EFF_DT = {">=$(=Date(vFromDate))<=$(=Date(vToDate))"} ,ELIG_AGE={'>=18'}>

+ <ELIG_GENDR_CD = {'M'},ELIG_TERM_DT = {">=$(=Date(vFromDate))<=$(=Date(vToDate))"} ,ELIG_AGE={'>=18'}>

+<ELIG_GENDR_CD = {'M'},ELIG_EFF_DT = {">=$(=Date(vFromDate))"},ELIG_TERM_DT = {"<=$(=Date(vFromDate))"} ,ELIG_AGE={'>=18'}>

<ELIG_GENDR_CD = {'M'},ELIG_EFF_DT = {">=$(=Date(vToDate))"},ELIG_TERM_DT = {"<=$(=Date(vToDate))"} ,ELIG_AGE={'>=18'}>

}

DISTINCT ELIG_PERSON_KEY))

)

Re: Error in Expression :(

Mon, 09 Dec 2013 06:02:08 GMT

This is what i have to check tresesco :

Condition #	Condition
1	EFF_DT between FROM_DT and TO_DT
2	TERM_DT between FROM_DT and TO_DT
3	FROM_DT between EFF_DT and TERM_DT
4	TO_DT between EFF_DT and TERM_DT
FROM_DT	TO_DT	EFF_DT	TERM_DT	To be Selected?
1/1/2013	12/31/2013	1/21/2013	1/21/2014	Yes
1/1/2013	12/31/2013	12/12/2012	12/12/2013	Yes
1/1/2013	12/31/2013	12/12/2012	12/12/2014	Yes
1/1/2013	12/31/2013	1/21/2013	6/21/2013	Yes
1/1/2013	12/31/2013	1/1/2012	12/31/2012	No
1/1/2013	12/31/2013	1/1/2014	6/30/2014	No

Re: Error in Expression :(

Anonymous — Mon, 09 Dec 2013 06:28:07 GMT

I think you should make a calendar consisting FROM_DT and TO_DT and join it with your base table,

Now you can easily compare all dates with eachother.