Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have this current table configuration. In the ACTION_CODES column, there are 2 values "START" and "COMPLETE". I sort of want to obtain a pivot table, that has these 2 values as columns, and the DateTime as their rows values. How can I do this?
This is the current layout of the table:
USER | DATE | DATETIME | ACTION_CODE | WORKCENTER | RESOURCE |
Mesys | 02.12.2023 | 02.12.2023. 06:04:56 | START | PACK01 | PACK02_01 |
Mesys | 02.12.2023 | 02.12.2023. 06:06:43 | COMPLETE | PACK01 | PACK02_01 |
Mesys | 02.12.2023 | 02.12.2023. 06:13:54 | START | PACK02 | PACK02_02 |
Mesys | 02.12.2023 | 02.12.2023. 06:16:12 | COMPLETE | PACK02 | PACK02_02 |
This is what I tried so far, but it doesn't work of course. Is there anybody that can help me?
// Load the original data
ActivityLog:
LOAD
ACTION_CODE,
Date(Floor(ACTIVITY_LOG_DATE_TIME)) as Date,
Timestamp(ACTIVITY_LOG_DATE_TIME) as DateTime,
ACTIVITY_LOG_USER as User,
SFC,
If(SubField(SFC,'.',2)='00','Main SFC','Sub-SFC') as MainSFC,
ACTIVITY_LOG_OPERATION,
SubField(ACTIVITY_LOG_OPERATION,'-',6) as Product,
ACTIVITY_LOG_RESRCE as Resource,
ACTIVITY_LOG_WORK_CENTER as WorkCenter,
RIGHT(ACTIVITY_LOG_WORK_CENTER,2) as CellID
FROM [ACTIVITY_LOG.qvd]
(qvd)
where not wildmatch(ACTION_CODE,'*ARCHIVE*');
// Pivot the data
PivotedActivityLog:
Pivot
Max(DateTime) as DateTime
using (ACTION_CODE, Date);
// Drop the temporary tables
Drop Table ActivityLog;
// Use PivotedActivityLog for further analysis or visualization
This is the desired result:
USER | DATE | WORKCENTER | RESOURCE | START | COMPLETE |
Mesys | 02.12.2023 | PACK01 | PACK02_01 | 02.12.2023. 06:04:56 | 02.12.2023. 06:06:43 |
Mesys | 02.12.2023 | PACK02 | PACK02_02 | 02.12.2023. 06:13:54 | 02.12.2023. 06:16:12 |
You should try to do this in the sheet, as far as I know there is no way to actually do this in the Load Script Editor.