topic Re: Interval Match in QlikView

Interval Match

Sat, 28 Jul 2012 05:31:51 GMT

I have two Time Periods, one the In time and the Oher the Out time. I have created a master sheet with the time frame and linked it with the table as 2 different time periods. Now i need make the two windows common to calculate a value using the values from these two window frames. Order is a common field which denotes the order of the time frame. I have used Interval Match to get values from the main table..

I have IN details as Below:

In Window	Order	Count
8-9	3	5
9-10	4	10
10-11	5	20
20-21	14	2
21-22	15	0

And the OUT Details as Below

Out Window	Order	Count
8-9	3	1
9-10	4	5
10-11	5	0
20-21	14	15
21-22	15	5

Now my calculation is between

8-9, availability is 5-1 =4

9-10, availability is (10+5)-(1+5) =9

10-11, availability is (10+20+5)-(1+0+5) =29

20-21, availability is (10+20+5+2)-(1+0+5+15) =16

How can i get the same?

Re: Interval Match

swuehl — Sat, 28 Jul 2012 11:39:52 GMT

You can concatenate your two tables into one fact table, using a common time window field and an additional type ('In', 'Out') field.

If your count values are not selection sensitive (e.g. by an additional product field), you can also generate a field that holds accumulated values.

This could look like:

IN:

LOAD * INLINE [

In Window Order Count

8-9 3 5

9-10 4 10

10-11 5 20

20-21 14 2

21-22 15 0

] (delimiter is '\t');

OUT:

LOAD * INLINE [

Out Window Order Count

8-9 3 1

9-10 4 5

10-11 5 0

20-21 14 15

21-22 15 5

] (delimiter is '\t');

FACT:

LOAD Order, Count, [In Window] as Window, 'In' as Dir, rangesum(Count,peek(Acc)) as Acc Resident IN order by Order;

LOAD Order, Count, [Out Window] as Window, 'Out' as Dir, rangesum(Count,if(recno()>1,peek(Acc))) as Acc Resident OUT order by Order;

drop tables IN, OUT;

If you create a table chart in your UI using Window as dimension, you can filter the In Window counts with a set expression like

=sum({<Dir = {In}>}Count)

which is similar to

=sum( if(Dir='In',Count))

but better performing.

You can addess your accumulated values (e.g. for the Out Window) like

=sum({<Dir = {Out}>}Acc)

Thus it's easy to create a table like requested.

If you need to be selection sensitive, you can do the accumulation all in the frontend, like

=rangesum(above(sum({<Dir = {In}>}Count),0,RowNo()))

Please see also attached,

Stefan

Re: Interval Match

Mon, 30 Jul 2012 10:05:01 GMT

Hi,

This is exactly what I wanted, but sorry that my table data had count in it. My count is not in the same table, that also has to be calculated from another table and then joined with the time table. Then how would I need to write the expression??

In Window

Order

8-9

9-10

Out Window

Order

8-9

9-10

Emp_Table:

Eng ID

Name

In_Date

Out_Date

8-9

Abc

9-10

bhy

Thanks & Regards,

Shilpa

Re: Interval Match

swuehl — Mon, 30 Jul 2012 10:10:08 GMT

Sorry, I think I don't completely understand.

Can you update my above sample script to your setting and repost it here?

Re: Interval Match

Mon, 30 Jul 2012 10:19:18 GMT

PFA.. I have two tables and now should connect both the tables as well as get a common window using the Order Option.

The Count included in your script is something I should calculate from the Emp Table and then use it in the table.

Thanks & Regards,

Shilpa

Re: Interval Match

Tue, 31 Jul 2012 06:20:20 GMT

Any Suggestions or ideas?

Re: Interval Match

swuehl — Wed, 01 Aug 2012 16:40:34 GMT

Can you describe how you need to derive the count from your employee table? That's not clear to me.

Re: Interval Match

Thu, 02 Aug 2012 04:06:42 GMT

Hi, Your script had the count in the Time Window only right.

//IN:

//LOAD * INLINE [

//In Window Order Count

//8-9 3 5

//9-10 4 10

//10-11 5 20

//20-21 14 2

//21-22 15 0

//] (delimiter is '\t');

In my case, the count is from the Employee table.

EMP_Table:

LOAD Emp_ID,

Emp_Name,

IN_Date,

OUT_Date

In Window Order

8-9 3 5

9-10 4

10-11 5

20-21 14

21-22 15

] (delimiter is '\t');

And so the below script although giving the values as I want will not work in this case.

LOAD Order, Count, as Window, 'In' as Dir, rangesum(Count,peek(Acc)) as Acc Resident IN order by Order;

LOAD Order, Count, as Window, 'Out' as Dir, rangesum(Count,if(recno()>1,peek(Acc))) as Acc Resident OUT order by Order;

I need one common window and I need to connect the Emp_ID from Emp table to the Window. So which expression would be right?

Thanks & Regards,

Shilpa

Re: Interval Match

Thu, 02 Aug 2012 05:54:31 GMT

Hi shilpa,

we can achive your requirement by using accumlation property in strainght table.

please find the attachament it solves your problem.

regrads,

Niranjan M.