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

# Multiple results when using logic statements

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!

• ###### 1. Re: Multiple results when using logic statements

Willis,

Check in the attached,

To get a single row per Buyer ID  you can use.

{FIXED [Buyer ID]: MIN( IF  [Order ID]  = [LOD-First Order ID] THEN UPPER( STR( [Contains X] ) ) end )}

On Sheet 3 , to compute the second order ID, I made the assumption that the second order ID was always a  1 increment  from the first, which is the case in your sample.

If this is  not  the case with your real data,  check on Sheet 4 where I took a different approach to compute the values  by using the index function.

Michel

1 of 1 people found this helpful
• ###### 2. Re: Multiple results when using logic statements

Your assumption with Sheet 4 is correct; I didn't think to make the Order IDs non-continuous for a given Buyer ID. It worked like a charm and I was able to create my Sets with much less hair loss. Thank you!