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.
