Hi Everyone, I have been struggling with transpose, join and batch tools to transform a table with timestamped values: 01/01/2019 0 02/01/2019 1 03/01/2019 0 04/01/2019 1 05/01/2019 10 06/01/2019 5 07/01/2019 3 08/01/2019 0 09/01/2019 1 10/01/2019 0 11/01/2019 0 12/01/2019 1 13/01/2019 0 14/01/2019 1 Into a table where each column is showing the 5 consecutive values from the date in the column header: 01/01/2019 02/01/2019 03/01/2019 04/01/2019 05/01/2019 09/01/2019 10/01/2019 0 1 0 1 10 … 1 0 1 0 1 10 5 … 0 0 0 1 10 5 3 … 0 1 1 10 5 3 0 … 1 0 10 5 3 0 1 0 1 Said otherwise, there is a one row lag from one column to another. And, of course, keeping in mind that the number of rows in the initial table might change and the number of rows in the final table (5 in the example) as well. Thanks 🙂

Breakdown one column into lagged columns

Hi Everyone,

I have been struggling with transpose, join and batch tools to transform a table with timestamped values:

01/01/2019	0
02/01/2019	1
03/01/2019	0
04/01/2019	1
05/01/2019	10
06/01/2019	5
07/01/2019	3
08/01/2019	0
09/01/2019	1
10/01/2019	0
11/01/2019	0
12/01/2019	1
13/01/2019	0
14/01/2019	1

Into a table where each column is showing the 5 consecutive values from the date in the column header:

01/01/2019	02/01/2019	03/01/2019	04/01/2019	05/01/2019		09/01/2019	10/01/2019
0	1	0	1	10	…	1	0
1	0	1	10	5	…	0	0
0	1	10	5	3	…	0	1
1	10	5	3	0	…	1	0
10	5	3	0	1		0	1

Said otherwise, there is a one row lag from one column to another.

And, of course, keeping in mind that the number of rows in the initial table might change and the number of rows in the final table (5 in the example) as well.

Thanks 🙂

Dynamic Processing

Accepted answers

danilang

Hi @pierrelouisbescond

This workflow gives you close to what you're looking for.

The record Id in the top branch will be used to get around the fact the final Cross tab tool will arrange output rows alphabetically The Number of Rows input contains the numbers of rows(X) in the final output. Use an Append fields to add the Number of output rows to each row in the data. The top branch duplicates each row in the input data X times, incrementing the MatchDate field and adding a sequential row number for each of these rows this expanded data is joined back to the original data on date = MatchDate. The Cross Tab tool pivots the data using RecordID as the column headers to get the output columns in the proper order. The Dynamic Rename changes the column names to the required ones, resulting in

Dan

Lagged.yxmd

All comments

danilang

Hi @pierrelouisbescond

This workflow gives you close to what you're looking for.

Dan

Lagged.yxmd

pierrelouisbescond

Hi @danilang

This workflow does not give something close to what I am looking for... but exactly what I was looking for!

The use of the "matchdate" intermediate variable is really clever... I had tried to simulate such function with a batch macro and it was awful compared to this elegant solution.

This is not the first time you are helping so big thank you again 🙂

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335