I have your typical CSV file from the bank. What I need to do is provide a pivot table that will have a column for all 12 months even though the data does not contain 12 different months; it’s no problem if the rows with no data are 0 or blank/null.
Hi,
@John, it is difficult to provide a better solution without any test data, but any way, hope the following solution put you on right track.
Transform file.
FixedTableLength.transform (3.7 KB)
1 Like
There is a Sequence transform for adding missing integer or date rows. If the month is an integer that might work. But I am a bit confused from your description about whether you want a row per month or a column per month. Some example data would help.
Can you please make dates that gets generated through Sequence Transform ISO standard. As at the moment it is 2023-1-1 when the month and day are single digit, which is not ISO standard, if you can make it like 2023-01-01 with two digits month and day then it will be ISO standard.
Reason asking for this is that SQLite only supports ISO standard date format and if I take data that is generated from Sequence Transform all the dates that are with single digit month and day are not recognized as Dates in date calculations.
Here is an example from sqlite
Also using Sequence Transform to generate missing months is not a better solution for this case, because it just adds rows which has no value a part from to get the Months and also one has to set the start and end date like 2023-01-31 and 2023-12-01 assuming if the data has no Jan and Dec and choosing 2023-01-31 and 2023-12-01 is to get the month without having to have all extra days which will be part of blank rows.
Best for this case is to have Columns from Jan to Dec and use them with Stack and no need to generate unnecessary rows.
Transform file.
FixedTableLengthWithSequence.transform (9.8 KB)
I will look into it. I wasn’t aware of the SQLite issue.
I depends on how the months are represented. If they are just integers from 1 to 12, it would work well.
I had a quick look at this. It tries to add new dates in the same formats as existing dates.
However 2023-12-1 is acceptable as both yyyy-MM-dd and yyyy-M-d. So you should make sure yyyy-MM-dd comes before yyyy-M-d in the Dates tab in Preferences. It should then work as you want. You can also use the DateTime Format transform.
I will add a bit more about this in the documentation.
1 Like
Thanks for the replies 
What I needed was a column for every month. After a while I got it to work by Stacking like you show but Stacking before the Pivot, which meant I had also to put zeros in the ‘amount’ field. It worked but your method is better. Thanks!
1 Like
I added all formats that could be in use, in the Dates tab and it is showing dates correctly with two digits for both Month and Day.
yyyy-MM-dd
yyyy-MMM-dd
yyyy/MM/dd
yyyy/MMM/dd
dd-MM-yyyy
dd-MMM-yyyy
dd/MM/yyyy
dd/MMM/yyyy
dd-MM-yy
dd-MMM-yy
dd/MM/yy
dd/MMM/yy
MM-dd-yyyy
MMM-dd-yyyy
MM/dd/yyyy
MMM/dd/yyyy
MM-dd-yy
MMM-dd-yy
MM/dd/yy
MMM/dd/yy
yyyy-M-d
yyyy/M/d
yy-M-d
yy/M/d
d-M-yyyy
d/M/yyyy
M-d-yyyy
M/d/yyyy
M-d-yy
M/d/yy
d-M-yy
d/M/yy
So if anyone wants to use it, they can simply copy from above and paste it in your Dates tab Preferences
Also thinking in lines of what you said, that if months are in number then the sequence will be helpful and indeed it is helpful, I changed my solution with the Sequence Transform from dates to number and indeed it is much simpler and less number of records, compare to if Date is used.
So what I did, using Copy Cols Transform made a copy of Date column, using DateTime Transform transformed the Date to number, used the Sequence Transform to generate number from 1 to 12 and then again using DateTime Format changed format from M to MMM and got the same results, but with fewer number of records compare to using Date in Sequence Transform.
But it needs quite number of transforms, compare to just using the Months as Headers directly with the Stack Transform
Also did not notice it before that in Transpose Transform there is option has header by clicking on that, it saved me from adding Header Transform.
Transform file.
FixedTableLengthWithSequence2.transform (9.8 KB)
1 Like
Having large amount of date formats could potentially slow down some transforms significantly. For example, the Summary transform will try every column with every date format to see if it is likely to be a date column or not. It tries to be as efficient as possible, but it could still be expensive.