Forum Discussion
Dataflow Convert from line to column with conditions
CroWorC ,
could you explain in more detail the logic with which you pass from the main table to the wanted one (keep in mind that, I don't know the meaning and the relations between the various acronyms used)?
As usual, Smauro 's solutions are elegant and instructive, but it seems that his truly admirable effort of interpretation has produced a slightly different result from what you described as wanted. To hope to have an effective solution also on the size of your case, you should also say how many rows, columns (and possibly sub-groups) it is.
Anonymous The data I have is a table from an application for following orders.
Every step an order makes in the proces is logged in this table.
The orders are projects build by diffrent contractors and in the building process there are two roughly two steps where something can get stucked.
The first part is between orderintake (Start) and communicating first promissed date (Plan1). The second part is between communicating first promissed date and order ready.
In both parts there can be an onhold situation because of legitime reason or because of unlegitime reason. The onhold time because of legitime reason will be compensated. An Onhold situation can occure more than one time per order. The time lost is in both parts diffrent. Before communicating plandate the total time between Start and communicating first promissed date minus the time OnHold is the total runtime and that is one of the KPI's
The second part is more difficult. An onhold situation frustrates the planning of the contractor so the time between the plandate by Onhold and the plandate directly given after the Offhold moment is the time to compensate. The application asks for a new plandate bij the OffHold action.
KPI: realize project on 1st promised date (including compensation)
KPI: number of replans <=2
Possible events:
wanted:
1 start
2 plan1 given
3 ready
not wanted
1 Start
2 OnHold legitime (date = 03/22/2021)
3 OffHold (date = 01/14/2021)
4 Plan given (this plandate is not relevat but after OffHold there must be a plandate)
5 Plan1 given (plandate 03/19/2021)
6 OnHold (legitime)
7 OffHold
8 Pladate (plandate 03/26/2021)
9 OnHold (not legitime)
10 OffHold
11 Plandate (plandate 04/02/2021)
12 OnHold (Legitime)
13 OffHold
14 Plandate (plandate 04/09/2021)
15 ready (date = 04/07/2021)
In the second example the compensation before giving the first plandate is 1 day,
The compensation after giving the first pandate is 7 days (step 5 and 😎 + 7 days (step 11 and 14) = 14 days.
The runtime of the order is total runtime - 15 days
The compensated plandate is the original plandate + 14 days (and in this case no score on 1th plandate)
In between the Events mentioned are diffrent other events for communication about the order.
In total there are about 150K orders with 3M events
The remoddeling of the table makes it possible for me to determine in witch part the event felt and how to compensate per order.