I have this data where in I would like to insert dates for every 6th column.
It has the same column name but the date changes.
Current Data:
F1 | VOLFCT | AHTFCT | FTEREQ | FTESCH | FTENET | SVLFCTADJ | VOLFCT2 | AHTFCT2 | FTEREQ2 | FTESCH2 | FTENET2 | SVLFCTADJ2 | VOLFCT3 | AHTFCT3 | FTEREQ3 | FTESCH3 | FTENET3 | SVLFCTADJ3 |
08:00:00 | 105 | 411.06 | 27.96 | 17 | -10.96 | 0 | 137 | 415.68 | 36.07 | 47 | 10.93 | 97.88 | 137 | 391.28 | 34.06 | 61 | 26.94 | 100 |
08:30:00 | 157 | 445.55 | 43.72 | 17 | -26.72 | 0 | 241 | 450.57 | 66.05 | 73.77 | 7.72 | 96.12 | 206 | 448.74 | 56.75 | 82 | 25.25 | 100 |
09:00:00 | 305 | 443.05 | 81.23 | 32 | -49.23 | 0 | 465 | 448.04 | 122.9 | 134.38 | 11.48 | 0 | 358 | 432.55 | 92.44 | 138.5 | 46.06 | 100 |
09:30:00 | 389 | 471.05 | 108.76 | 25.77 | -82.99 | 0 | 569 | 476.34 | 158.53 | 159.24 | 0.71 | 0 | 454 | 482.7 | 129.2 | 149.5 | 20.3 | 15.03 |
10:00:00 | 473 | 475.54 | 132.44 | 43.31 | -89.13 | 0 | 621 | 480.9 | 174.14 | 178.56 | 4.42 | 0 | 496 | 509.86 | 148.44 | 176.33 | 27.89 | 57.01 |
Expected Output:
1/12/2023 | 1/12/2023 | 1/12/2023 | 1/12/2023 | 1/12/2023 | 1/12/2023 | 1/13/2023 | 1/13/2023 | 1/13/2023 | 1/13/2023 | 1/13/2023 | 1/13/2023 | 1/14/2023 | 1/14/2023 | 1/14/2023 | 1/14/2023 | 1/14/2023 | 1/14/2023 | |
F1 | VOLFCT | AHTFCT | FTEREQ | FTESCH | FTENET | SVLFCTADJ | VOLFCT2 | AHTFCT2 | FTEREQ2 | FTESCH2 | FTENET2 | SVLFCTADJ2 | VOLFCT3 | AHTFCT3 | FTEREQ3 | FTESCH3 | FTENET3 | SVLFCTADJ3 |
08:00:00 | 105 | 411.06 | 27.96 | 17 | -10.96 | 0 | 137 | 415.68 | 36.07 | 47 | 10.93 | 97.88 | 137 | 391.28 | 34.06 | 61 | 26.94 | 100 |
08:30:00 | 157 | 445.55 | 43.72 | 17 | -26.72 | 0 | 241 | 450.57 | 66.05 | 73.77 | 7.72 | 96.12 | 206 | 448.74 | 56.75 | 82 | 25.25 | 100 |
09:00:00 | 305 | 443.05 | 81.23 | 32 | -49.23 | 0 | 465 | 448.04 | 122.9 | 134.38 | 11.48 | 0 | 358 | 432.55 | 92.44 | 138.5 | 46.06 | 100 |
09:30:00 | 389 | 471.05 | 108.76 | 25.77 | -82.99 | 0 | 569 | 476.34 | 158.53 | 159.24 | 0.71 | 0 | 454 | 482.7 | 129.2 | 149.5 | 20.3 | 15.03 |
10:00:00 | 473 | 475.54 | 132.44 | 43.31 | -89.13 | 0 | 621 | 480.9 | 174.14 | 178.56 | 4.42 | 0 | 496 | 509.86 | 148.44 | 176.33 | 27.89 | 57.01 |
Solved! Go to Solution.
Hello,
You could generate a row with the dates and then union the two tables.
To generate a row with dates that increase by 1 day after 6 days, I would probably do the following:
Start with a text input with the first date you want to start with:
Then generate rows to match the number of columns you need:
Please note that I started with 0 rather than 1 since I will use this value for the formula.
I then updated the date field with a formula that uses the row count to determine how many days we should add:
datetimeadd([Date],floor([RowCount]/6),'days')
I then used a cross tab to turn the date column into a row:
It worked! Thank you so much!