Shifting data in a table

[AlBoo8749]

Is there a way either using script or a chart to shift data in a table?

Currently the data looks like this:

Period	State	End of Month	Col 1	Col 2	Col 3	Col 4	Col 5	Col 6	Col 7	Col 8	Col 9	Col 10	Col 11	Col 12
1	DC	1/31/2022	1	2	3	4	5	6	7	8	9	10	11	12
2	DC	2/28/2022	1	1	2	3	4	5	6	7	8	9	10	11
3	DC	3/31/2022	1	1	1	2	3	4	5	6	7	8	9	10
4	DC	4/30/2022	1	1	1	1	2	3	4	5	6	7	8	9
5	DC	5/31/2022	1	1	1	1	1	2	3	4	5	6	7	8
6	DC	6/30/2022	1	1	1	1	1	1	2	3	4	5	6	7
7	DC	7/31/2022	1	1	1	1	1	1	1	2	3	4	5	6
8	DC	8/31/2022	1	1	1	1	1	1	1	1	2	3	4	5
9	DC	9/30/2022	1	1	1	1	1	1	1	1	1	2	3	4
10	DC	10/31/2022	1	1	1	1	1	1	1	1	1	1	2	3
11	DC	11/30/2022	1	1	1	1	1	1	1	1	1	1	1	2
12	DC	12/31/2022	1	1	1	1	1	1	1	1	1	1	1	1

But I want the table to look like this:

Period	State	End of Month	Col 1	Col 2	Col 3	Col 4	Col 5	Col 6	Col 7	Col 8	Col 9	Col 10	Col 11	Col 12
1	DC	1/31/2022	1	2	3	4	5	6	7	8	9	10	11	12
2	DC	2/28/2022	1	2	3	4	5	6	7	8	9	10	11
3	DC	3/31/2022	1	2	3	4	5	6	7	8	9	10
4	DC	4/30/2022	1	2	3	4	5	6	7	8	9
5	DC	5/31/2022	1	2	3	4	5	6	7	8
6	DC	6/30/2022	1	2	3	4	5	6	7
7	DC	7/31/2022	1	2	3	4	5	6
8	DC	8/31/2022	1	2	3	4	5
9	DC	9/30/2022	1	2	3	4
10	DC	10/31/2022	1	2	3
11	DC	11/30/2022	1	2
12	DC	12/31/2022	1

[Chanty4u]

Hi 

You can try something like below

LOAD 

    Period, 

    State, 

    [End of Month], 

    [Col 1], 

    [Col 2], 

    [Col 3], 

    [Col 4], 

    [Col 5], 

    [Col 6], 

    [Col 7], 

    [Col 8], 

    [Col 9], 

    [Col 10], 

    [Col 11], 

    [Col 12],

    Peek([Col 1]) + [Col 1] AS [Shifted Col 1], 

    Peek([Col 2]) + [Col 2] AS [Shifted Col 2], 

    Peek([Col 3]) + [Col 3] AS [Shifted Col 3], 

    Peek([Col 4]) + [Col 4] AS [Shifted Col 4], 

    Peek([Col 5]) + [Col 5] AS [Shifted Col 5], 

    Peek([Col 6]) + [Col 6] AS [Shifted Col 6], 

    Peek([Col 7]) + [Col 7] AS [Shifted Col 7], 

    Peek([Col 8]) + [Col 8] AS [Shifted Col 8], 

    Peek([Col 9]) + [Col 9] AS [Shifted Col 9], 

    Peek([Col 10]) + [Col 10] AS [Shifted Col 10], 

    Peek([Col 11]) + [Col 11] AS [Shifted Col 11], 

    Peek([Col 12]) + [Col 12] AS [Shifted Col 12]

FROM [YourTable];

[AlBoo8749]

I am sorry the mistake is mine. The numbers I had in there were dummy numbers. I just wanted to know if there was a way to keep the first row the same, but have the second row shift to the left one field and third row shift to the left two fields and so on. Your code works well if I in fact wanted to have the same numbers that were in my example.

Again the fault is mine for not explaining my issue clearly.

Thank you!

[AlBoo8749]

Here is a better example of what I am trying to do.

Here is the original table:

Period	State	EOMDate	ret1	ret2	ret3	ret4	ret5	ret6	ret7	ret8	ret9	ret10	ret11	ret12
1	DC	31-Jan-21	1.945205479	1.913894325	1.878669276	1.859099804	1.843444227	1.819960861	1.819960861	1.808219178	1.780821918	1.765166341	1.741682975	1.706457926
2	DC	28-Feb-21	1.970149254	1.970149254	1.953091684	1.936034115	1.918976546	1.893390192	1.87206823	1.850746269	1.837953092	1.833688699	1.825159915	1.820895522
3	DC	31-Mar-21	1.947286822	1.947286822	1.947286822	1.925581395	1.903875969	1.888372093	1.863565891	1.83255814	1.810852713	1.804651163	1.795348837	1.792248062
4	DC	30-Apr-21	1.941558442	1.941558442	1.941558442	1.941558442	1.915584416	1.896103896	1.883116883	1.876623377	1.857142857	1.847402597	1.840909091	1.827922078
5	DC	31-May-21	1.942470389	1.942470389	1.942470389	1.942470389	1.942470389	1.925549915	1.898477157	1.881556684	1.868020305	1.851099831	1.847715736	1.837563452
6	DC	30-Jun-21	1.946708464	1.946708464	1.946708464	1.946708464	1.946708464	1.946708464	1.921630094	1.918495298	1.909090909	1.89968652	1.887147335	1.880877743
7	DC	31-Jul-21	1.953405018	1.953405018	1.953405018	1.953405018	1.953405018	1.953405018	1.953405018	1.935483871	1.913978495	1.903225806	1.888888889	1.88172043
8	DC	31-Aug-21	1.970588235	1.970588235	1.970588235	1.970588235	1.970588235	1.970588235	1.970588235	1.970588235	1.937908497	1.918300654	1.905228758	1.905228758
9	DC	30-Sep-21	1.970873786	1.970873786	1.970873786	1.970873786	1.970873786	1.970873786	1.970873786	1.970873786	1.970873786	1.948220065	1.938511327	1.922330097
10	DC	31-Oct-21	1.967793881	1.967793881	1.967793881	1.967793881	1.967793881	1.967793881	1.967793881	1.967793881	1.967793881	1.967793881	1.945249597	1.93236715
11	DC	30-Nov-21	1.946714032	1.946714032	1.946714032	1.946714032	1.946714032	1.946714032	1.946714032	1.946714032	1.946714032	1.946714032	1.946714032	1.921847247
12	DC	31-Dec-21	1.972972973	1.972972973	1.972972973	1.972972973	1.972972973	1.972972973	1.972972973	1.972972973	1.972972973	1.972972973	1.972972973	1.972972973

Here is the end product:

Period	State	EOMDate	ret1	ret2	ret3	ret4	ret5	ret6	ret7	ret8	ret9	ret10	ret11	ret12
1	DC	31-Dec-20	1.945205479	1.913894325	1.878669276	1.859099804	1.843444227	1.819960861	1.819960861	1.808219178	1.780821918	1.765166341	1.741682975	1.706457926
2	DC	31-Jan-21	1.970149254	1.953091684	1.936034115	1.918976546	1.893390192	1.87206823	1.850746269	1.837953092	1.833688699	1.825159915	1.820895522
3	DC	28-Feb-21	1.947286822	1.925581395	1.903875969	1.888372093	1.863565891	1.83255814	1.810852713	1.804651163	1.795348837	1.792248062
4	DC	31-Mar-21	1.941558442	1.915584416	1.896103896	1.883116883	1.876623377	1.857142857	1.847402597	1.840909091	1.827922078
5	DC	30-Apr-21	1.942470389	1.925549915	1.898477157	1.881556684	1.868020305	1.851099831	1.847715736	1.837563452
6	DC	31-May-21	1.946708464	1.921630094	1.918495298	1.909090909	1.89968652	1.887147335	1.880877743
7	DC	30-Jun-21	1.953405018	1.935483871	1.913978495	1.903225806	1.888888889	1.88172043
8	DC	31-Jul-21	1.970588235	1.937908497	1.918300654	1.905228758	1.905228758
9	DC	31-Aug-21	1.970873786	1.948220065	1.938511327	1.922330097
10	DC	30-Sep-21	1.967793881	1.945249597	1.93236715
11	DC	31-Oct-21	1.946714032	1.921847247
12	DC	30-Nov-21	1.972972973

[AlBoo8749 · Accepted solution]

I have figured out how to accomplish this. I added a column that counted the number of periods to help choose the correct row since using 'RowNo' and 'RecNo' chose the rows and records as they were loaded not how I sorted them. I was then able to enter the following in each column adjusting the formulas depending on the column and period. Field names are 'ret#.'

If(Period=1,ret1,
If(Period=2,ret2,
If(Period=3,ret3,
If(Period=4,ret4,
If(Period=5,ret5,
If(Period=6,ret6,
If(Period=7,ret7,
If(Period=8,ret8,
If(Period=9,ret9,
If(Period=10,ret10,
If(Period=11,ret11,
If(Period=12,ret12,
If(Period=13,ret13,
If(Period=14,ret14,
If(Period=15,ret15,
If(Period=16,ret16,
If(Period=17,ret17,
If(Period=18,ret18,
If(Period=19,ret19,
If(Period=20,ret20,
If(Period=21,ret21,
If(Period=22,ret22,
If(Period=23,ret23,
If(Period=24,ret24,
If(Period=25,ret25,
If(Period=26,ret26,
If(Period=27,ret27,
If(Period=28,ret28))))))))))))))))))))))))))))