Comparison

[Nikkhil]

Hello,

I have a comparison between two sheets containing grocery items in the last month and this month.

The columns are - 

Name

Type

Rate

Quantity

The requirement is to know -

1. If there are any items that are added in this month list, it should be marked as "Added" in a new column in the final output.

2. If  there are any items that are updated for example, a change in rate or quantity, it should be marked as "Updated" in the column.

3. If there are any items that are removed in the previous month, it should be marked as "Deleted".

4. If there is no change in the item in this month list, it should be marked as "No Change"

Example

May -

Name	Type	Price	Quantity
Item A	Kitchen	10	1
Item B	Toiletries	30	3
Item C	Garage	40	4

June -

Name	Type	Price	Quantity
Item A	Kitchen	10	1
Item B	Toiletries	35	3
Item D	Garden	40	4

The output should be

Name	Type	Price	Quantity	Action
Item A	Kitchen	10	1	No Change
Item B	Toiletries	40	3	Updated
Item C	Garage	40	4	Deleted
Item D	Garden	40	4	Added

I tried to achieve it through outer join between this month and last month but I was not able to find a way to add the action column in my final output.

Any pointers would be helpful. Thanks!!

[T_Willins]

Hi @Nikkhil,

Without just giving the answer, you are on the right track Joining the data, however you will need the data from all three output anchors for the solution.  If May is coming in the Left input then what does not join and comes out the Left output would be what is deleted.  A formula can help with labeling.  As for updated information you will need to compare the joined data to appropriately label it.  Then you need to bring all the data back together and format it.  Hopefully this helps.  Working through the data manipulation is the best way to learn Alteryx tools.

[rahul_Kumar_koshika]

Hi @Nikkhil,

I have built workflow which meet your end requirement. Please find the below attached workflow for your reference.

If your purpose as meet. Please mark it as solution

Thanks,

 

[grazitti_sapna]

Hi @Nikkhil,

Here is my take on your problem

PFA workflow. I hope it helps

[Nikkhil]

Thanks, Rahul and Sapna. It worked fine but there is a possibility that one item can be used in different areas and can have different rates and quantity (Apologies for not sharing this part earlier).

May -

Name	Type	Price	Quantity
Item A	Toiletries	60	1
Item A	Kitchen	10	1
Item B	Toiletries	30	3
Item C	Garage	40	4

June -

Name	Type	Price	Quantity
Item A	Toiletries	60	1
Item A	Kitchen	10	2
Item B	Toiletries	35	3
Item D	Garden	40	4

The output starts showing duplicate records.

Thanks!!

[rahul_Kumar_koshika]

Hi @Nikkhil,

Thanks for your reply.

Can you please elaborate what you are exactly looking for. If you can share the expected output for the sample data shared by you.

[grazitti_sapna]

Hi @Nikkhil,

Is this the output you are looking for?

I have updated the workflow to remove the duplicates, however toiletries appear two times once for item A and the other time for item B.

Let me know if this solves your issue.

If this is not the output that you are looking for, please share the expected output with us.

[Nikkhil]

Thank you, Rahul and Sapna for your help.

@rahul_Kumar_koshika - The expected output is same as what Sapna has shared.

@grazitti_sapna - Mine is a 2019.4 version and I am not able to import the package. Can you please tell me what are the changes you have made to address duplicate rows and I can make them at my end? Is there a way to make a workflow built in a higher version compatible with a lower version? 

Thanks!!

[Nezrin]

To update the version of the workflow, you can open the file as notepad and update the version. Save the file and open as an alteryx workflow - you will be good to use.

[Nikkhil]

Hi Sapna,

It worked fine but when I added more columns to it, the duplicates started appearing again. Can you please tell me the logic you have implemented for removing duplicate rows so that I can extend the same to the newer columns?

Thanks!!