topic Re: How to write this logic in set-analysis in QlikView

How to write this logic in set-analysis

Anonymous — Mon, 15 Sep 2014 17:30:45 GMT

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

Re: How to write this logic in set-analysis

Anonymous — Mon, 15 Sep 2014 17:54:42 GMT

Any ideas ??

Re: How to write this logic in set-analysis

cspencer3 — Mon, 15 Sep 2014 18:10:55 GMT

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.

Re: How to write this logic in set-analysis

Anonymous — Mon, 15 Sep 2014 18:25:18 GMT

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

Re: How to write this logic in set-analysis

cspencer3 — Mon, 15 Sep 2014 18:35:01 GMT

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

Re: How to write this logic in set-analysis

Mon, 15 Sep 2014 19:43:03 GMT

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.

Re: How to write this logic in set-analysis

Anonymous — Tue, 16 Sep 2014 02:22:34 GMT

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

Re: How to write this logic in set-analysis

Tue, 16 Sep 2014 20:09:22 GMT

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