How to make multiple line items into a single line item?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Best Practices
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@crazybeauti_fulWhy don't you summarize
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@crazybeauti_fulcould you please test the attached WF
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
