Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi together,
I work in the data load editor and have data in the following pattern:
SubscriptionID | CustomerID | date | x | y | z |
1 | 10100 | 21.02.2017 | - | 1 | - |
2 | 10100 | 21.02.2017 | - | - | 1 |
1 | 10100 | 22.02.2017 | - | 1 | - |
2 | 10100 | 22.02.2017 | - | - | 1 |
3 | 10100 | 22.02.2017 | 1 | - | - |
4 | 10100 | 22.02.2017 | 1 | - | - |
A customer can have multiple subscriptions. And as long as the subscription is active a row with a new data for each subscription is created. There are 3 subscription models. A customer can have a subscription model more then once.
I want to merge rows in such a way that I have a row for each CustomerID and date. The result should look similar than this:
CustomerID | date | x | y | z |
10100 | 21.02.2017 | - | 1 | 1 |
10100 | 22.02.2017 | 1 | 1 | 1 |
If a customer has multiple subscriptions of the same type its enough to let it be displayed as a '1' the amount of times does not matter. The '1' can be seen as a 'yes' while a null means not subscriped to that model. I tried using only as aggregation but twice or more of the same subscription model would display null then. Other then that concatenate and max didn't work either. If I'm correct I need to make a group by statement on CustomerID and date. But how can i aggregate the data?
Many thanks to anyone in advance for helping.