Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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.

1 Solution

Accepted Solutions
vgutkovsky
Honored Contributor II

Re: Filling empty values for WIP counting

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

3 Replies
vgutkovsky
Honored Contributor II

Re: Filling empty values for WIP counting

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

Not applicable

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.

vgutkovsky
Honored Contributor II

Re: Re: Filling empty values for WIP counting

Pretty simple modification. Your date island can remain at the day level (no need to take it to the timestamp level). Small change to the chart expression to incorporate function dayend(). See updated example, attached.

Regards,

Vlad

Community Browser