Hi Qlik experts!
I am currently working on creating a sankey diagram of system statuses extracted from SAP PM. The data is structured like this:
For an order I know which status was set or deleted at what time (example data):
Order | System status | Date |
1 | A | 01-01-18 |
1 | B | 04-01-18 |
2 | A | 05-01-18 |
1 | C | 06-01-18 |
2 | C | 07-01-18 |
3 | A | 07-01-18 |
3 | B | 08-01-18 |
3 | C | 12-01-18 |
Using the QlikView sankey extension, I would like to display the flow of orders in system statuses. For this I need the field source, destination and a count of orders. For the example data shown above this would look like the following:
Source | Destination | No. of orders |
A | B | 2 |
B | C | 2 |
A | C | 1 |
But I cannot figure out how to get my data structured this way. I have considered grouping by order, and then counting the number of orders in the expression, but so far no luck.
Thanks in advance!