I have a list of project reports with an issue date and a project topic plus as measure their traffic light r/a/g. What I want to generate is a pivot table that lists vertically all topics and horizontally the Status of the report per issue date.
Unfortunately, the topics are not covered in each issue of the report, so typically every topic occurs only everey 5-6 reports and my line per topic has only an entry every few columns.
What I need is some kind of sequence number to use as the horizontal dimension. The date of the report can be displayed as text in the field maybe. Ideally, the dummy table below would have only 2 columns as all projects have only 2 occurences. Instead of "datum" I would sort by sequence number then.
This is how the raw data looks :
Lines 1-12 are the first appearance of the topic in the reporting, line 13-13 the second for each topic.
Any hint how to do it? Maybe there are even better ways but currently the automatic calculation of a sequence number is the best idea I have.
During load, I could first sort by project and then by date/datum. And then the lines should get a sequential number for each project starting by 1 to x, depending on how often a status was reported. Or is there something I could use out of the box?