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?
