Hi,
In my attached screenshot, I am getting 4 line items for a single account. The txn colums are the individual txns comprising the sum columns. How do I make it like this?
Sum A Sum B Sum C Txn A Txn B Txn C
29944.06 -5240.21 2994.41 14972.03 -5240.21 2994.41
29944.06 -5240.21 2994.41 14972.03 0 0
Or something like that. Basically, I need to delete duplicate rows as much as possible. Some accounts have 72 line items but most are just duplicate lines.
Thank you!
Solved! Go to Solution.
I think you just want to use a Unique tool with all the fields selected.
It would be beneficial to see more of the data set but based on your question, this seems to be all you need to do.
Thanks, let me know if this works!
Hi @Blake ,
I am using a Unique tool in my workflow. I actually tried different fields combinations to fix this but no luck. I've attached another sample. Ideally, I should only have 3 lines if columns 6 & 8 are aligned.
Thank you,
crazybeauti_ful
Hi,
I suspect you will have to first combine column 6 and 8 then dedup based on this column.
It will be really helpful if you can attach a sample of the data instead.
Cheers,
Seinchyi
Take a look at the crosstab tool. You specify which columns tell you that you have a unique row, and how to summarize the other information on the line.
hi all,
I've attached more samples for my dilemma but here's a quick look:
Current State:
Name | Date 1 | Code | Date 2 | Amount 1 | Amount 2 | Lookup |
A1 | 2020-04-02 | B1 | 2020-05-04 | 2237.02 | 0.00 | A1B12020-04-022020-05-04 |
A1 | 2020-04-02 | B1 | 2020-05-04 | 0.00 | -559.25 | A1B12020-04-022020-05-04 |
A1 | 2020-04-02 | B2 | 2020-05-04 | 7448.94 | 0.00 | A1B22020-04-022020-05-04 |
A1 | 2020-04-02 | B2 | 2020-05-04 | 0.00 | -1862.23 | A1B22020-04-022020-05-04 |
Expectation:
Date 1 | Code | Date 2 | Amount 1 | Amount 2 | Lookup |
2020-04-02 | B1 | 2020-05-04 | 2237.02 | -559.25 | A1B12020-04-022020-05-04 |
2020-04-02 | B2 | 2020-05-04 | 7448.94 | -1862.23 | A1B22020-04-022020-05-04 |
I'd really appreciate all your helping hands!
Thank you.
@crazybeauti_fulWhy don't you summarize
Hi @deviseetharaman ,
I think summarize can work per my initial test. I used max and min to do it but this is only assuming it only 2 transactions. How can I include all transactions if there are more than 2?
Thank you!
@crazybeauti_fulcould you please test the attached WF
Hi @deviseetharaman ,
I think I'm getting there (thank you!!!) but I have one last question (sorry!!).
My actual data looks like this. I need 2 columns for each Desc --
1. total
2. individual amounts
I've got the total column correctly but I had trouble on the individual amounts because they are not lined up correctly (multiple rows attachment earlier). Your workflow will work if I only have max of 2 amounts per Desc but how do I do it if I have > 2 amounts like in the expectation table?
Super thank you!!!
Current State:
Name | Date 1 | Amount | Code | Date 2 | Desc | Lookup |
A1 | 20200519 | 1681.59 | B1 | 20200521 | D | A1B12020051920200521 |
A1 | 20200519 | -420.4 | B1 | 20200521 | T | A1B12020051920200521 |
A1 | 20200519 | 252.24 | B1 | 20200521 | R | A1B12020051920200521 |
A1 | 20200519 | 2555.69 | B1 | 20200521 | D | A1B12020051920200521 |
A1 | 20200519 | -894.49 | B1 | 20200521 | T | A1B12020051920200521 |
A1 | 20200519 | 638.92 | B1 | 20200521 | R | A1B12020051920200521 |
A1 | 20200519 | 800 | B1 | 20200521 | D | A1B12020051920200521 |
A1 | 20200519 | 500 | B1 | 20200521 | D | A1B12020051920200521 |
A1 | 20200519 | 200 | B1 | 20200521 | T | A1B12020051920200521 |
Expectation:
Name | Date 1 | Code | Date 2 | Lookup | D_Sum | D | T_Sum | T | R_Sum | R |
A1 | 20200519 | B1 | 20200521 | A1B12020051920200521 | 5537.28 | 1681.59 | -1514.89 | -420.4 | 891.16 | 252.24 |
A1 | 20200519 | B1 | 20200521 | A1B12020051920200521 | 5537.28 | 2555.69 | -1514.89 | -894.49 | 891.16 | 638.92 |
A1 | 20200519 | B1 | 20200521 | A1B12020051920200521 | 5537.28 | 500 | -1514.89 | -200 | 891.16 | 0 |
A1 | 20200519 | B1 | 20200521 | A1B12020051920200521 | 5537.28 | 800 | -1514.89 | 0 | 891.16 | 0 |