5 Replies Latest reply: Jun 24, 2013 4:27 PM by Steve Lord RSS

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

      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!

          • Re: Straight table:  marking users who have a status that meets two conditions?
            Steve Lord

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

              • Re: Straight table:  marking users who have a status that meets two conditions?
                Steve Lord

                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.

                • Re: Straight table:  marking users who have a status that meets two conditions?
                  Steve Lord

                  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
                • Re: Straight table:  marking users who have a status that meets two conditions?

                  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.