Straight table: marking users who have a status that meets two conditions?

[#1]

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!

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[stevelord]

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.)

[stevelord]

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.

[#5]

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.

[stevelord]

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