Ah, here's a script solution!
First, you break the things out to their own fields like so:
if(ActivirtyEntry='Pedometer', 'Pedometer') as Pedometer,
if(ActivirtyEntry='Electronic', 'Electronic') as Electronic,
Then, you do a formula on those new fields in a resident load like so:
if(Pedometer='Pedometer' AND Electronic='Electronic', Dual('Both',1),
if(Pedometer='Pedometer' AND Electronic=Null(), Dual('Pedometer',2),
if(Pedometer=Null() AND Electronic='Electronic', Dual('Electronic',3)))) as SuperActivityMethod
Drop Table FirstTable;
Drop Field ActivityEntry; //if you really don't want this field now you have replacement for it
Then on your table, just use SuperActivityMethod as the dimension.
(This should crush the caveats related to columnar data being on separate rows and also make the formula contingent on direct value of activity entry itself.)
Sorry you need to wrap that with a SUM() and then put a Group By at the end of the first load. The Group by needs to include all of the fields loaded outside the SUM(if ()) statements
SUM(if(ActivirtyEntry='Pedometer', 'Pedometer')) as Pedometer,
SUM(if(ActivirtyEntry='Electronic', 'Electronic'))as Electronic,
FROM filepath Group fields not in the sum/if aggregation statements
The subsequent resident load should work as is after that.
Here's the final draft of what worked. The Nulls were wrong to use in the resident load formula as well, becaue the SUM(if()) statements turned nulls into 0s.
LOAD UserID as UserId,
SUM(if(ActivityEntry='Pedometer', 1)) as Pedometer,
SUM(if(ActivityEntry='Electronic', 1)) as Electronic
(ooxml, embedded labels, table is Sheet1) Group by UserID;
if(Pedometer=1 AND Electronic=1, Dual('Both',1),
if(Pedometer=0 AND Electronic=1, Dual('Electronic',2),
if(Pedometer=1 AND Electronic=0, Dual('Pedometer',3)))) as SuperActivityMethod
Drop Table FirstTable;
(copied below from resulting table box)
UserId SuperActivityMethod Electronic Pedometer 10001 Electronic 1 0 10022 Both 1 1 10134 Pedometer 0 1
Hi, this looks like it works, thanks!
What does <> surrounding UserID in your formula do? Looks like if(count(total <UserID> UserID)>1,'Both',ActivityEntry).
So I am assuming your formula counts whether the UserID appears more than once, and if so, it returns 'Both', otherwise, which of the single ActivityEntry it is.