Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Interval Match

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 WindowOrderCount
8-935
9-10410
10-11520
20-21142
21-22150

And the OUT Details as Below

Out WindowOrderCount
8-931
9-1045
10-1150
20-211415
21-22155

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?

8 Replies
MVP
MVP

Re: Interval Match

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

Not applicable

Re: Interval Match

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

MVP
MVP

Re: Interval Match

Sorry, I think I don't completely understand.

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

Not applicable

Re: Interval Match

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

Not applicable

Re: Interval Match

Any Suggestions or ideas?

MVP
MVP

Re: Interval Match

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

Not applicable

Re: Interval Match

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

Not applicable

Re: Interval Match

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.

Community Browser