List items not purchased by customer

[akasubi]

Hi,

I have 2 lists of data.

1. List of customers' purchases

2. Full list of items

Customer	Item	Amount
A	123	1
A	456	1
A	789	1
B	123	1
B	456	1
C	789	1
D	456	1
D	789	1

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.

Customer	Item	Amount
A	123	1
A	456	1
A	789	1
B	123	1
B	456	1
B	789	0
C	123	0
C	456	0
C	789	1
D	123	0
D	456	1
D	789	1

Thanks!

afk

[atcodedog05]

Hi @akasubi ,

Here is a workflow for the task.

Output:

Workflow:

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]

I would suggest:

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]

Hi @akasubi,

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

[Assaf_m]

Please find below the solution 

attached workflow for your reference

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

[akasubi]

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 · Accepted solution]

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)

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

All joins are outer joins

Hope this helps 🙂

[GaneshBo · Accepted solution]

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!

Best,
Ganesh

[atcodedog05]

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]

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

And actually everyone else who contributed!