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

alespooletto — Wed, 06 Dec 2023 06:56:55 GMT

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

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

johnwalmond — Wed, 06 Dec 2023 08:11:32 GMT

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.

topic Re: How to create a Pivot Table with values of a column as Column and DateTime as rows? in App Development

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

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