Hi,
I am trying to derive a column based on some matching data in the same table. This is to be displayed in a pivot table as a dimension ultimately. Currently, I have a calendar table with dates and timeslots (hours) and I also have an orders table with matching dates and timeslots. I have used a concatenated key with the date and the timeslots to join the two tables and up to this point it works.
In theory we can have up to 3 matches per timeslot. In order to display multiple matches in the pivot table I am using the concat function around the desired column value as the measure. This does work but the issue with this is that the data is displayed as one row and I need to split it out on to multiple rows.
I would like to derive a column called for example "delivery slot". In this column I would like to store the following values;
The first match for a timeslot would have the value 1 or 'slot one', the second match would have the value 2 or 'slot 2' and the third match would have the value 3 or 'slot 3'. This new derived column would be second row dimension in the pivot table.
The image attached shows an example of two entries that match the same timeslot and should be displayed as separate lines but due to the lack of the derived "delivery slot" dimension do not display very well.
I have been unable so far to find a method to do this reliably. Is this possible and what would be the best approach?
