3 Replies Latest reply: Jun 25, 2014 10:25 AM by Vlad Gutkovsky RSS

    Filling empty values for WIP counting

      Hello,

       

      I have a problem where I want to know my amount of works in WIP on a certain date (end of day).

       

      I have table which consist of work order number and time stamps when ever the status of the work has changed. And there are certain statuses which are marked as WIP statuses (in this example B, C). And status of the work is valid until it is updated again.

       

      WorknroTimestampStatusWIP
      1231.7.2014A0
      1231.7.2014B1
      1236.7.2014D0
      3452.7.2014A0
      3454.7.2014B1
      3454.7.2014C1
      3456.7.2014D0
      3457.7.2014E0

       

      So 1.7 WIP=1. As the latter status stands in the end of day.

      2.7. WIP=1.

      ..

      4.7. WIP=2 as both works are in WIP status.

      ..

       

      Also the Statuses of the work don't necessary go from A>B>C>D, but can have all the combinations eg. A>C>B>C>D, A>D..  Only thing that is certain is that every work at the end has the Status which is not WIP status.

       

      One solution in my mind is to fill the empty dates whenever the status is WIP status.

      WorknroTimestampStatusWIP
      1231.7.2014A0
      1231.7.2014B1
      1232.7.2014B1
      1233.7.2014B1
      1234.7.2014B1
      1235.7.2014B1
      1236.7.2014D0
      3452.7.2014A0
      3454.7.2014B1
      3454.7.2014C1
      3455.7.2014C1
      3456.7.2014D0
      3457.7.2014E0

      But this seems inefficient, as there is a huge amount of works..

       

      So please advice if you have better solution.

        • Re: Filling empty values for WIP counting
          Vlad Gutkovsky

          Juha,

           

          That's a pretty advanced scenario, but is definitely possible. I would tackle this with a date island in the data model. I'm assuming you already have a regular calendar attached to your Fact table. Clone this calendar and rename the fields so it's not linked to anything in the data model.

           

          QUALIFY *;

          DateIsland:

          LOAD * RESIDENT Calendar;

          UNQUALIFY *;

           

          Your expression will need to incorporate both calendars (the regular one and this new island) and will be pretty heavy because it will need to use nested aggr statements.

           

          count(distinct aggr(

                  if(min(total <Worknro> Timestamp) <= DateIsland.Timestamp,

                        only(aggr(

                              if(Timestamp=max(total <Worknro,DateIsland.Timestamp> if(Timestamp<=DateIsland.Timestamp,Timestamp)) and max(WIP)=1,

                                      Worknro

                            )

                            ,Worknro,DateIsland.Timestamp,Timestamp

                        ))

                  )

                  ,Worknro,DateIsland.Timestamp

          ))

           

          The dimension of the table would be the date island date. An example is attached. If you find that this expression is too heavy, another approach could be to take the logic to the data model itself using intervalmatch and pre-aggregation. You would lose the ability to make dynamic filters (e.g. selecting a subset of work orders) but would definitely gain speed.

           

          Regards,

          Vlad

            • Re: Filling empty values for WIP counting

              Thanks for the example.

               

              Almost there. I now came up with problem that I have two or more status changes in one day.

               

              Example:

              WorkNoTimeStampStatusWIPID
              W12313.06.2014, 12:31:02.614109453769
              W12316.06.2014, 13:39:17.661109465562
              W12316.06.2014, 13:39:23.183219465564
              W12316.06.2014, 13:39:46.334K09465571
              W12318.06.2014, 08:51:03.167R09480310

              So in this case the work has never been in WIP stage in the end of any day. But now it shows for this WorkNo WIP status on 16.6 & 17.6. In the Load script TimeStamp is formatted to date format (DD.MM.YYYY).

               

              In the Load script the data is loaded in "right" order. Or to say that in database the data is in timestamp order, every new status modification comes up with new timestamp (and unique ID).

               

              So the question is how to handle this.. Should I create every second also to the DateIsland and Load the data with exact time? Or can the Load order used somehow? As the status of the work should be in the end of day the last status valid.