Identify Opportunity in the dataset by extracting list of customers based on a logic

[udles5666]

Hi Community,

I am stuck with a problem and was hoping to get some assistance. 

I have a dataset in which I am trying to identify opportunities. I need to be able to extract a list of customer names who have not ordered products flagged as Must Have Products in the last 60 days. Also, would need the product names (opportunity) at the row level. 

Can someone please help? 

Attached package for reference & expected output example. P.S - It is not certification related query.

Thanks in advance.  

[Qiu]

@udles5666 

I am not quite sure I understand your logic but just want to give a blind shot.

Please let me know your comment.

[udles5666]

Hi @Qiu 

Thanks heaps for getting on to this & helping. 

The track is right until the Left Join. I need the products which are not in the transaction table.

Like always your solutions always give me ideas. So, thought of running past through to you and seek further help. Can we use product ID & customer ID to create a composite key and look up the transaction table? Then we can place the join on that & the R anchor will return the list products & store that does not exist in the transaction table? 

I couldn't make the composite key work. If you think my solution should work can you please help me with the composite key part? Attached is the updated workflow, I have also simplified the dataset. 

Thanks & most appreciated, 

[Qiu · Accepted solution]

@udles5666 
Good to know I helped a bit.
In alteryx, we use operator '+' to concatenate 2 data strings.

In this case, the [Custom ID] is a Numeric field so we need to convert it to String.

[udles5666]

@Qiu - The composite key solution worked. The only downside is the multiplication of the dimensions. I ended up with lots of records for my original dataset. But that's not a bad trade off to make. 

Thanks again. Much appreciated. 

[Qiu]

@udles5666 
Thank you for the feedback and good to know it works somehow...😁