Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have a situation where a user may have entered their data in three ways: through a pedometer, an electronic tracker, or both.
On my straight table, I am trying to mark the users appropriately, but I can't seem to get my expression correct.
UserID ActivityEntry
10134 Pedometer
10001 Electronic
10022 Pedometer
10022 Electronic
So you can see that I have two users who entered their activity with via a single method, but then I have a user who entered their activity via BOTH methods. I want to essentially delete the ActivityEntry column and display only a column that shows "Pedometer" or "Electronic" if they entered activity that way, or a "Both" if the user entered the activity via both methods.
Any help would be appreciated, thanks!
See attached example.
Ah, here's a script solution!
First, you break the things out to their own fields like so:
FirstTable
LOAD
if(ActivirtyEntry='Pedometer', 'Pedometer') as Pedometer,
if(ActivirtyEntry='Electronic', 'Electronic') as Electronic,
FROM filepath
Then, you do a formula on those new fields in a resident load like so:
LOAD *,
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
Resident FirstTable;
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
FirstTable
LOAD
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.
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.
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.
FirstTable:
LOAD UserID as UserId,
SUM(if(ActivityEntry='Pedometer', 1)) as Pedometer,
SUM(if(ActivityEntry='Electronic', 1)) as Electronic
FROM
C:\Users\path\Book1.xlsx
(ooxml, embedded labels, table is Sheet1) Group by UserID;
FinalTable:
LOAD *,
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
Resident FirstTable;
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 |