Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

List items not purchased by customer

akasubi
8 - Asteroid

Hi,

 

I have 2 lists of data.

 

1. List of customers' purchases

2. Full list of items

 

CustomerItemAmount
A1231
A4561
A7891
B1231
B4561
C7891
D4561

D

7891

 

Item
123
456
789

 

As you can see, customers don't purchase all items. How can I output the data to display each item across all customers such that items that aren't purchased display an amount of 0? Please note that I'm using the In-DB tools with data from SQL Server.

 

Expected result below with the new rows highlighted.

 

CustomerItemAmount
A1231
A4561
A7891
B1231
B4561
B7890
C1230
C4560
C7891
D1230
D4561
D7891

 

Thanks!

afk

10 REPLIES 10
atcodedog05
22 - Nova
22 - Nova

Hi @akasubi ,

 

Here is a workflow for the task.

 

Output:

atcodedog05_0-1602747537592.png

Workflow:

atcodedog05_1-1602747566507.png

 

Please use in-db stream-in and stream-out to integrate this.

We cannot build you a in-db workflow without us having the connection strings.

 

Hope this helps 🙂

 

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

jdunkerley79
ACE Emeritus
ACE Emeritus

I would suggest:

jdunkerley79_0-1602747612908.png

 

First use a summarise tool to get all the customer IDs

Then use an append fields tool to create a complete set

You can then join the purchase data to this complete set

The right output will contain the extra rows - using a formula tool to add a 0 for Amount

Next union the Join to this and you will have the complete set

Emil_Kos
17 - Castor
17 - Castor

Hi @akasubi,

 

I have build something similar to what @atcodedog05 has done.

 

Emil_Kos_0-1602747713401.png

 

Assaf_m
11 - Bolide

Please find below the solution 

 

attached workflow for your reference

if this solves your problem, please mark it as a solution.

 

 

 

Assaf_m_0-1602747791201.png

 

akasubi
8 - Asteroid

Hi @atcodedog05.

 

Thanks for your suggestion. However I'm using in-db tools with data coming from SQL server. How would I do this using the in-db tools?

 

afk

atcodedog05
22 - Nova
22 - Nova

Hi @akasubi 

 

In the below workflow all the tool are available in In-DB tool palate too.

I have replaced append tool with join tool (cross join)

atcodedog05_1-1602748275581.png

 

 

You will be able to replicate this with In-DB tools

atcodedog05_0-1602748393064.png

All joins are outer joins

 

Hope this helps 🙂

 

GaneshBo
Alteryx
Alteryx

Hi @akasubi ,

 

Below would be what a solution with In-DB tools look like. I have attached a sample solution using normal tools, which you can use to translate to In-DB.

 

Hope this helps!

GaneshBo_0-1602749337393.png

 

 

Best,
Ganesh

atcodedog05
22 - Nova
22 - Nova

Hi @akasubi 

 

As @GaneshBo  mentioned my workflow would also translate to the same workflow with In-Db tools.

 

Great Job @GaneshBo in taking the extra efforts to create a temp table in your database and build the workflow 🙂

Without connecting to a db you wont be that successful in building an In-Db workflow because the columns wont populate 😅

akasubi
8 - Asteroid

Thank you to you both @atcodedog05 @GaneshBo, this has worked.

 

And actually everyone else who contributed!

Labels