Total Amount Not Repeating in every Row

[Mwatts23]

Hi,

I hope you can help in finding a solution to my query. I don't need the Total Amount to keep on repeating in every row but I need to keep the total number of rows that I have in my Raw data .

I attached the file for the Raw data, the Issue and the expected result.

Please see screenshot below.

Many thanks,

Mike

[Qiu]

@Mwatts23 
You want to do it by Alteryx? what do you get so far?

I think we can use a Multi-row formula tool to remove the duplicated results after first occurence.

[WirkKarl]

Hi,

I hope someone can help me with this issue.

In my workflow, I have a column with the Total Amount, but it repeats on every row. What I want is to keep the total number of rows from my raw data, but show the total amount only once — for example, in the first row — and leave the other rows blank or null.

I’ve attached an example file showing the raw data, the issue, and the expected result.

Thanks in advance!

[abacon · Accepted solution]

@Mwatts23 Here is a way to do it.

Join the main data to the summarized data on the min start date, this will only append the record once to your data, then do a left join using a union tool and it will bring the full stream back together. If you want to change the date format

Bacon

[Qiu]

@abacon 
Nice and elegant one. It turns out that Multi-row formula might not be good approach. 😂

[flying008]

Hi, @Mwatts23 

FYI.

Account Name	City	Start Date	End Date	Amount	Min_Start Date	Max_End Date	Sum_Amount
A	London	1-Jan-22	1-Mar-22	1	1-Jan-22	1-Mar-22	3
A	London	1-Oct-22	1-Apr-23	2
A	Singapore	1-Mar-22	1-May-23	3	1-Jul-23	1-Nov-23	12
A	Singapore	1-Jul-23	1-Nov-23	4
A	Singapore	1-May-22	1-Jul-22	5
B	Paris	23-Jan	24-Oct	10	23-Aug	24-Oct	21
B	Paris	23-Aug	24-Nov	11
B	New York	22-Jul	24-Dec	12	22-Jul	25-Jan	39
B	New York	22-Oct	25-Jan	13
B	New York	23-May	25-Feb	14

[J-Li]

You create a unique key to each record,  and then use a Summarize tool to get the first occurrence (minimum key) for each columns combination you want no to repeat. Next, use a Join tool to get the desired result.

[Mwatts23]

Thank you Everyone for the solutions you shared. It was all very helpful! 😎