Transformation Pivot: Fix columns

[Kai Lattmann]

Hello,

when I create a Transformation Pivot table, the pivot made colums for each month, what is in the database.

But it is possible to create colums of month, which is not in the database I want to see the whole year in the pivot, without creating dummy rows for the missing month.

In Access you can create a fix range of columns, that will shown the whole time, not only when there is data for it.

Is there any solution for this problem

Thanks for helping me!

Kai

[Kevin Meier]

I am interessted in the same... I solved it with some dummy calculated columns and replace them... which is for sure not the best

[David Boot-Olazabal]

Hi Kevin,

This situation only happens when values are missing for the same period (data table). If there are missing values for multiple products in different months, you can display all months properly (data table (2)).
See the difference between the two data tables below:

Is your situation/data set up according to the Data Table view (missing data for both products in October 2024)?

If that is the case, you could solve this with a dummy table, holding all the months for 2024. Join that dummy table to the Data Table (Full outer join) and you will get the extra row you're missing:

The above image shows the dummy table months, which is joined (add columns) to the Data Table on a full outer join, resulting in having the October month row getting added to the final data set.

Kind regards,

David

[Olivier Keugue Tadaa]

Hi @Kevin Meier and @Kai Lattmann
As you pointed out, Pivoting in Spotfire is done on existing data. To have all the months, you will have to add "dummy rows" containing the missing month's values. 

Usually, our approach consists of starting with a "generic" data table containing the expected output column values (the months in your case), which we then combine with the actual and dynamic data from the database (using the "Add Rows" features).

[barchiel33]

As others have said, you'll typically need to create the dummy rows before you can pivot them. However, you could also use a Python Data Function to create the columns you'd want. I'm not sure the exact code that you'd need to use, since I don't have your data. But I would think you could take your data as an input, create a pandas dataframe with Month columns using the calendar module, and then aggregate/pivot the input data table to match the columns. Alternatively, you could use the data function to just create an empty table with the month columns and join your data in using the data canvas.

[Jonthan T.]

I agree with what others have said. It is super easy to create the dummy records of just the months in an excel sheet, and add them from clipboard with a full outer join. Then the dummy data is automatically saved in the file without having to point to a shared file location somewhere.