Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

How to make multiple line items into a single line item?

Highlighted
8 - Asteroid

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!

Highlighted
Alteryx Certified Partner

Hi @crazybeauti_ful 

 

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!

Highlighted
8 - Asteroid

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

 

 

Highlighted
Alteryx
Alteryx

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

Highlighted
7 - Meteor

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.

Highlighted
8 - Asteroid

hi all,

 

I've attached more samples for my dilemma but here's a quick look:

 

Current State:

 

NameDate 1CodeDate 2Amount 1Amount 2Lookup
A12020-04-02B12020-05-042237.020.00A1B12020-04-022020-05-04
A12020-04-02B12020-05-040.00-559.25A1B12020-04-022020-05-04
A12020-04-02B22020-05-047448.940.00A1B22020-04-022020-05-04
A12020-04-02B22020-05-040.00-1862.23A1B22020-04-022020-05-04

 

Expectation:

 

Date 1CodeDate 2Amount 1Amount 2Lookup
2020-04-02B12020-05-042237.02-559.25A1B12020-04-022020-05-04
2020-04-02B22020-05-047448.94-1862.23A1B22020-04-022020-05-04

 

I'd really appreciate all your helping hands!

 

Thank you.

 

Highlighted
11 - Bolide

@crazybeauti_fulWhy don't you summarize  

Highlighted
8 - Asteroid

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!

Highlighted
11 - Bolide

@crazybeauti_fulcould you please test the attached WF

Highlighted
8 - Asteroid

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:

 

NameDate 1AmountCodeDate 2DescLookup
A1202005191681.59B120200521DA1B12020051920200521
A120200519-420.4B120200521TA1B12020051920200521
A120200519252.24B120200521RA1B12020051920200521
A1202005192555.69B120200521DA1B12020051920200521
A120200519-894.49B120200521TA1B12020051920200521
A120200519638.92B120200521RA1B12020051920200521
A120200519800B120200521DA1B12020051920200521
A120200519500B120200521DA1B12020051920200521
A120200519200B120200521TA1B12020051920200521

 

Expectation:

 

NameDate 1CodeDate 2LookupD_SumDT_SumTR_SumR
A120200519B120200521A1B120200519202005215537.281681.59-1514.89-420.4891.16252.24
A120200519B120200521A1B120200519202005215537.282555.69-1514.89-894.49891.16638.92
A120200519B120200521A1B120200519202005215537.28500-1514.89-200891.160
A120200519B120200521A1B120200519202005215537.28800-1514.890891.160
Labels