2 Replies Latest reply on Sep 16, 2016 10:17 AM by Willis Dell

    Multiple results when using logic statements

    Willis Dell

      Hi all,

       

      I've been scouring the web for a solution to my question with no success so far. I have a table containing order information, that I'm looking to crunch some numbers on based on some key fields:

       

      1. Buyer ID: Who the customer is

      2. Order ID: KEY of the table, increments by one

      3. Order Date: Date when Order ID was generated

      4. Contains X: Boolean that checks a given order for X and returns T/F accordingly

       

      The end goal is to create a cohort of buyers based on the 6 possible combinations of 'Contains X' for their first two orders. (T/T, F/F, T/F, F/T, T/NULL, F/NULL)

       

      Steps taken:

      1. Create an LOD to return a Buyer's first Order Date using {FIXED [Buyer ID]: MIN([Order Date])}  (LOD-First Order Date)

      2. Filter only Buyers who have made their first order after a chosen time

      3. Create an LOD to return a Buyer's first Order ID using {FIXED [Buyer ID]: MIN([Order ID])} (LOD-First Order ID)

      4. Return the value from [Contains X] for the Order ID returned in #3

      5. I have tried adding [Contains X] to the view without success and created a calculated field:

      IIF([Order ID] = [LOD-First Order ID],[Contains X],NULL)

      IF [Order ID] = [LOD-First Order ID] THEN STR([Contains X]) END

       

      Neither of which have been successful, as the resulting table will return two rows for a given Buyer ID if two orders have been placed on a given day in which [Contains X] may be both True and False. Filtering on the calc from #5 won't work, as I can remove NULLS, but that will only serve as a bandaid once the second order information is added to the table.

       

      At the end of the day, I would like to have a table that presents the following information:

      |  Buyer ID  |  First Order ID  |  Contains X (for <--)  |  Second Order ID  |  Contains X (for <--)  |

       

      Attached is a quick mockup of the data and thanks in advance!