Cleansing Duplicate Data - DIFFICULT

Question

Hi,

I'm finding this very challenging and not even sure if possible.

Using the attached data, under each header of the data, i.e. "Jan-2023", "Feb - 2023" and so on, there is many rows of data.

A lot of these rows are duplicates.

Is there any way I can just have one set of each number?

So, for example, for "Jan-2023", there is 2421 rows of data, summing to -447,448 in total.

However, if you filter on that column, there's actually only 15 numbers which are being repeated.

Those numbers are -60,912, -11,566, -6,750, -1,229, -136, -87, -26, 26, 87, 136, 1,229, 1,978, 11,591, 16,577 and 18,503.

I only want one set of each of these numbers and the same rule to apply to all other months.

At the moment, I have multiple of these numbers.

Hope this makes sense!

Raw Data - Difficult.xlsx

danilang · Accepted Answer

Hi @AlteryxLearner23

Looking at your data, you have 16 different key lines, with variations in the following fields

Type
Forecast Type
Functional Currency
Local Currency
Identify Cross-Currency Leases ("FALSE")
Final Line Item Currency

Assuming that you want to capture these unique rows, here's a way to find the unique values.

Transpose all the month columns to give all the key fields plus a name and value field representing the months and the corresponding values.  Group by all columns to get the unique values.  The bottom branch adds a column order to the columns to account for the fact that the Cross Tab tool will rearrange the month columns alphabetically.  After joining to add the column order field, cross tab to get the values back into monthly columns, but using the column order field as the column names.  Finally, rename to get the original Monthly column names

The result is the sixteen key rows with the unique values from each

Dan

OllieClarke · Accepted Answer

@AlteryxLearner23

Here's a couple of approaches depending on what you exactly mean

If you use a summarise tool and group by every column, then that will give you every unique combination of values across all fields (so

Col 1Col 2AAABBABAAB

Would become

Col 1Col 2AAABBA

The transpose approach will give you every unique value in each column, but treating each column independently

So

Col 1Col 2AAABBABAAB

would become

Col 1Col 2AABB

My preferred structure for this type of data is having each date and value in individual rows, rather than columns, so I put that option in there for you too.

Hope that helps,

Ollie