Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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;
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.
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,
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;