Breakdown one column into lagged columns

[pierrelouisbescond]

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 🙂

[danilang · Accepted solution]

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

[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 🙂

[danilang]

You're very welcome, @pierrelouisbescond 

Dan