Interval Match

[#1]

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?

[swuehl]

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

[#3]

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

1

9-10

2

Out Window

Order

8-9

1

9-10

2

Emp_Table:

Eng ID

Name

In_Date

Out_Date

8-9

Abc

5

1

9-10

bhy

3

2

Thanks & Regards,

Shilpa

[swuehl]

Sorry, I think I don't completely understand.

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

[#5]

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

[#6]

Any Suggestions or ideas?

[swuehl]

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

[#8]

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

[#9]

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.