Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Set Analysis using Exists and OR

Hi Community,

I'm trying to replicate the following logical rule in QlikView using Set Analysis:

(appointments where the starting planned date is less than the current report date

     AND appointments where the created date is less than the current report date)


AND

(appointments where the actual starting is greater than the current report date

     OR appointment is planned)

Essentially the SQL statement looks like (assuming the report date is 2013-06-30):

WHERE [Planned_Appointment_Start_Date] < '2013-06-30'

  AND [Appointment_Created_Date] < '2013-06-30' 

          AND ([Appointment_Start_Date] > '2013-06-30'

                         OR [Is_Planned_Appointment] = 1)


Can anyone please assist?

Thanks in advanced.

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: Set Analysis using Exists and OR

Thanks for you input guys, but the correct set analysis in the end was:

{<Planned_Appointment_Start_Date = {"<'2013-06-30'"},

          Appointment_Created_Date = {"<'2013-06-30'"},

          Appointment_Start_Date = {">'2013-06-30'"}>

+ <Appointment_Created_Date = {"<'2013-06-30'"},

          Planned_Appointment_Start_Date = {"<'2013-06-30'"},

          Is_Planned_Appointment = {1}>}

Because the Planned_Appointment_Start_Date and Appointment_Created_Date are outside the or bracket, they must be applied to both.

Thanks again for the input.

3 Replies
msheraton
Contributor III

Re: Set Analysis using Exists and OR

See the attached file

MVP
MVP

Re: Set Analysis using Exists and OR

Hi

Something like this:

{<Planned_Appointment_Start_Date = {"<'2013-06-30'"},

          Appointment_Created_Date = {"<'2013-06-30'"},

          Appointment_Start_Date = {">'2013-06-30'"}>

+ <Is_Planned_Appointment = {1}>}

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Set Analysis using Exists and OR

Thanks for you input guys, but the correct set analysis in the end was:

{<Planned_Appointment_Start_Date = {"<'2013-06-30'"},

          Appointment_Created_Date = {"<'2013-06-30'"},

          Appointment_Start_Date = {">'2013-06-30'"}>

+ <Appointment_Created_Date = {"<'2013-06-30'"},

          Planned_Appointment_Start_Date = {"<'2013-06-30'"},

          Is_Planned_Appointment = {1}>}

Because the Planned_Appointment_Start_Date and Appointment_Created_Date are outside the or bracket, they must be applied to both.

Thanks again for the input.

Community Browser