How to write this logic in set-analysis

[Anonymous]

Hi Experts,

I need your help to write an expression using set-analysis.

I need to calculate the Events based on date condition.. In my database i need to check the stats of event on every week start date..

below is the query to calculate events for a single week start date.. i have a field weekstart date.. so the same logic i want to calculate for each weekstart date..

Select Count (Event)

where CODE = 'India'

AND WeekStart <= TO_DATE('14-07-2014', 'DD-MM-YYYY')

AND (Complete_Date >= TO_DATE('14-07-2014', 'DD-MM-YYYY')

  OR Cancel_Date >= TO_DATE('14-07-2014', 'DD-MM-YYYY')

  OR (Complete_Date IS NULL AND Cancel_Date IS NULL))

From Event_table;

In the above query the difficult part to transform in to set analysis is the OR inside AND..

Please help me to solve this logic..

I have attached the sample data and qvw..

Thanks

Aashish

[Anonymous]

Any ideas ??

[cspencer3]

Could you possibly create that logic using an IF statement in the load script which is used to Flag the record. Then in your set only count records that have that Flag set to True? I do this sometimes when i need and/or logic instead of putting it into the Set Analysis which can get complex pretty quickly.

[Anonymous]

Thanks Charles,

But how I will get the comparison of various dates with week start date...

I need to calculate this expression for every week start date..

Any kind of help is highly appreciated

[cspencer3]

I must have misunderstood. Is there any way you can post a sample of the data structure?

[#6]

Hi Aashish,

I can't open the attached Event.qvw file since I am using personal edition.

So can you help me understand what this condition does when counting events ?

AND WeekStart <= TO_DATE('14-07-2014', 'DD-MM-YYYY')

or perhaps based on the excel data, can you show the output that is expected e.g.

[Anonymous]

Hi Jain,

WeekStart Date i need to put as dimension and the below query gives the count of all events having weekstart date less than or equal to weekstart date and complete_date >= weekstart date OR Cancel_Date >= weekstartdate

Select Count (Event)

where CODE = 'India'

AND WeekStart <= TO_DATE('14-07-2014', 'DD-MM-YYYY')

AND (Complete_Date >= TO_DATE('14-07-2014', 'DD-MM-YYYY')

  OR Cancel_Date >= TO_DATE('14-07-2014', 'DD-MM-YYYY')

  OR (Complete_Date IS NULL AND Cancel_Date IS NULL))

From Event_table;

Hope this clears the logic..

Aashish

[#8]

Hi Aashish,

I think I have a Solution without using set analysis, albeit tricky one.

First lets break down the conditions;

AND WeekStart <= TO_DATE('14-07-2014', 'DD-MM-YYYY')

WeekStart is by same name in the application,

TO_DATE('14-07-2014', 'DD-MM-YYYY') is named WeekStart2

This particular condition is managed during Load of the table.

Since we want to know that for a given Week, how many weeks are smaller than it (to consider those events), I had to create another field WeekStart2 with Cartesian product.

EventData:

LOAD Event,

     WeekStart,

     [Complete Date],

     [Cancel Date]

FROM

EventData.xlsx

(ooxml, embedded labels, table is Sheet3);

Outer Join(EventData)

LOAD distinct WeekStart as WeekStart2

FROM

EventData.xlsx

(ooxml, embedded labels, table is Sheet3)

;

Then, resident load and apply the first condition

NoConcatenate

EventData1:

Load Event,

     WeekStart,

     if(WeekStart<=WeekStart2,WeekStart2) as WeekStart2,

     [Complete Date],

     [Cancel Date]

Resident EventData;

Finally remove rows that has WeekStart2 empty as condition is not satisfied

NoConcatenate

EventData2:

Load *

Resident EventData1

where WeekStart2 <> Null();

Now the rest of the conditions;

AND (Complete_Date >= TO_DATE('14-07-2014', 'DD-MM-YYYY')

  OR Cancel_Date >= TO_DATE('14-07-2014', 'DD-MM-YYYY')

  OR (Complete_Date IS NULL AND Cancel_Date IS NULL))

This is handled in fairly simple way in chart expression

Count(if(

          ([Complete Date] >= WeekStart2

          OR [Cancel Date] >= WeekStart2

          OR (IsNull([Complete Date]) and IsNull([Cancel Date]))), Event

         )

     )

Here is the result :-

I suspect there is a down side that it may not work if volume of data is high.

Do try this out and let me know if it works for you.

Regards,

Kalpesh Jain