Dynamically subtract two columns.

Question

I have two data like below

Dataset1:

CurrencyQuarterValueUSDQ1 2470JPYQ1 2433INRQ1 24

66EURQ1 2465

Dataset2:

CurrencyQuarterValueUSDQ1 2370JPYQ1 2333INRQ1 23

66EURQ1 2365

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

I want the ouput as like below,

CurrencyQ1 23Q1 24DiffUSD6070-10JPY2233-11INR5066-16EUR4565-20

TUSHAR050392 · Answer

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

DataNath · Answer

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.

DynamicCurrencyDiffs.yxmd

Alteryxexpert · Answer

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.