8 Replies Latest reply: Aug 2, 2012 1:54 AM by niranjan RSS

    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?

        • Re: Interval Match
          Stefan Wühl

          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

              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

                • Re: Interval Match
                  Stefan Wühl

                  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

                      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

                          Any Suggestions or ideas?

                            • Re: Interval Match
                              Stefan Wühl

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

                                • 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

                      • 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.