Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Convert two rows having data into 2 column data

MadhavTR
7 - Meteor

Hi all, I have a transaction dataset in which amount is in line 1 and tax is in line 2. I want to bring this into 1 line with side by side column having amount and tax. The key is the invoice num (or reference in my case)

 

Input

Invoice numtax codeUSDamount
123P1100
123P11000

 

Expected output

invoice numtax codeAmountTax
123P11000100
6 REPLIES 6
OTrieger
13 - Pulsar

@MadhavTR 
Will the 2nd value in this table will always be the Amount  or the rows might alternate?

MadhavTR
7 - Meteor

It will always be in the amount.

 

Kaustubh17
9 - Comet

Hi @MadhavTR 

 

Please find the attached SC of the desired solution:

  • Sample the data in the text tool
  • group by tax code, invoice number
  • First, last the values.
  • rename the field name to amount and tax

If this solution resolved your query, please mark it as solved to assist others with similar issues.

 

Best regards,

Kaustubh

OTrieger
13 - Pulsar

@MadhavTR 
In this way the most easiest way will be to use the Summarize Tool.
Group the data on Invoice Number and Tax code and Concatenate USAmount.
Then use Parse Text to Columns Tool and you will get the data in that manner.
What left is to rename the 2 fields, you can use Select tool to do that 

RJ_1988
5 - Atom
 

1) Add one identifier Column called as 'Type' (it can be dynamic based on the pattern).

2) Use a cross Tab tool - Group Data by 'Invoice Number' and 'Tax Code' , change Column headers 'Type' and Values for new columns as 'USDAmount', method for aggregating values 'Sum', you will get the desired result.

MadhavTR
7 - Meteor

Hi Kaustubh,

 

Thanks for your reply last time. I have now got 2 extra usecases on top it

 

1.Can i add and summarize based on certain conditions (here gst code will always be present)

2. Also take data from a certain column where the gstcode is missing (this will be true always)

for example

 

Input

Invoice NumDescriptiongstcodeUSDAmountTAXID
123TaxP110 
123TaxP110 
123AmountP1100 
123AmountP1100 
123   ABCD

 

Output

Invoice Numtax codeAmountTaxTax ID
123P120020ABCD

 

Thanks in advance

Labels
Top Solution Authors