Turning one Work Task into multiple ones

[sogloqlik]

Hi all,

First of all, no, it is not about QMC task.

Please see the attached data example.

I have the following requirement:

My data is a table with tasks (every task has a unique ID)

Every task has stages (a to i) with start timestamp and end Time stamp.

I need to split each task to 3 separate tasks.

the way to do that is as follows:

From stage a to d - Task 1

Stage e   - Task 2

From Stage f to I -Task 3

Another table should be created with the new tasks and start timestamp and end timestamp with the following logic:

The end time of stage 1 will be taken from the start time of stage 2, 

The end time of stage 2 will be taken from the start time of stage 3.

But there are times when not all the stages were performed Hence won't appear in the data) and I Still have to split them. In this case, the end time of the last task would be Today () & '17:00:00'.

Again, I've attached an example with different scenarios.

Any ideas are welcomed.

Regards,

[Sayed_Mannan]

try this,

FinalTable:

LOAD *,
If(Stage >= 'a' and Stage <= 'd', 'Task 1',
        If(Stage = 'e', 'Task 2',
              If(Stage >= 'f' and Stage <= 'i', 'Task 3'))) as TaskType,
If(Stage >= 'a' and Stage <= 'd', EndTime,
         If(Stage = 'e', StartTime,
                If(Stage >= 'f' and Stage <= 'i', StartTime))) as StartTime,
If(Stage >= 'a' and Stage <= 'd', StartTime,
        If(Stage = 'e', EndTime,
                If(Stage >= 'f' and Stage <= 'i', EndTime))) as EndTime;
LOAD *
FROM Table;

for the scenario where not all stages were performed, you can add an additional condition to check if the EndTime is null and if so, set it to Today() & '17:00:00'

I hope this helps.

[sogloqlik]

Thank you for your answer, I will try it.

I want to add another complexity: in some cases, stage e would be done before stage a but will still have to be onsidered as task_2, How would you adjust the code?

Regards,

[Sayed_Mannan · Accepted solution]

based on your new use case I'll update my script like this:

FinalTable:
LOAD *,
If(Stage = 'e', 'Task 2',
If(Stage >= 'a' and Stage <= 'd', 'Task 1',
If(Stage >= 'f' and Stage <= 'i', 'Task 3'))) as TaskType,
If(Stage = 'e', StartTime,
If(Stage >= 'a' and Stage <= 'd', EndTime,
If(Stage >= 'f' and Stage <= 'i', StartTime))) as StartTime,
If(Stage = 'e', EndTime,
If(Stage >= 'a' and Stage <= 'd', StartTime,
If(Stage >= 'f' and Stage <= 'i', EndTime))) as EndTime;
LOAD *
FROM YourTable;

// For the scenario where not all stages were performed
NoStages:
LOAD *,
If(IsNull(EndTime), Today() & '17:00:00', EndTime) as EndTime
Resident FinalTable;