How to create a Pivot Table with values of a column as Column and DateTime as rows?

[alespooletto]

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

[johnwalmond]

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.