Dynamically subtract two columns.

[Alteryxexpert]

I have two data like below

Dataset1:

Currency	Quarter	Value
USD	Q1 24	70
JPY	Q1 24	33
INR	Q1 24	66
EUR	Q1 24	65

Dataset2:

Currency	Quarter	Value
USD	Q1 23	70
JPY	Q1 23	33
INR	Q1 23	66
EUR	Q1 23	65

The data will be dynamic here, Quarter will get changed to Q2 24 & Q2 23 Dynamically.

I want the ouput as like below,

Currency	Q1 23	Q1 24	Diff
USD	60	70	-10
JPY	22	33	-11
INR	50	66	-16
EUR	45	65	-20

[DataNath]

Hey @Alteryxexpert, how do you get on with this? We:

1) Transpose the data to get the values in a single column - this will also happen in field order

2) Use a Multi-Row Formula to take away the first value from the second

3) Sum the [Diff], grouped by [Currency]

4) Join this back to the original data to add the new field

[Alteryxexpert]

I have edited the post with changed requirement.

[DataNath]

Ok, just add a couple of Cross-Tabs to get the initial shape & join.

[Alteryxexpert]

Problem here is I wont get all currencies in dataset 1 available always in dataset 2. Consider Data set 1 having 4 currencies and dataset 2 has 3 currencies and I need to achieve this, how to do.

[DataNath]

Can you please provide an actual sample of what your data looks like/could look like? Instead of the back & forth on limited information? If that's the last possible issue then you can just join on Currency initially & conduct a Union.

[TUSHAR050392]

Hey @Alteryxexpert Alternate approach which will account for any missing data in dataset 2-

1.Use cross tab to convert both of your data. Select Group by the currency, Change column headers to Quarter and Values header to the Value and select sum as aggregate method.

2. Join the two datasets on currency followed by a union tool in which you will insert the left and join stream of join tool as this will create a left join to cater any values in dataset 1 that are not in dataset 2.

3. Then you will use a formula tool to create the difference column which will simply be subtraction of your two value columns. You will also have to use data cleansing before formula to convert Null to 0.

Hope this helps