Create New Data Row by dividing two values from the existing rows.?

[kiotsuresh]

Hi,

I have Data like below where i need to populate the third row by dividing the values of row 1 and row 2 , Revenue = Profit/Sales, How to achieve this through Alteryx. I need to Populate the Value as well as new name Revenue from the existing data set. 

Item	month	Classification	Value	Category	Name
Chair	Jan - 2022	Furniture	50000	Home Furniture	Profit
Chair	Jan - 2022	Furniture	50	Home Furniture	Sales
Chair	Jan - 2022	Furniture	1000 = 50000/50	Home Furniture	Revenue

[ShankerV]

@kiotsuresh 

Please let me know if you are looking for the similar result.

Step 1: Input the data

Step 2: Sort, as you might have clumsy data.

Step3: Using RecordID to have unique values in each row

Step 4:

Step 5:

Step 6:

Step 7: Union tool

Step 8: Sorting to have formatted output

Kindly accept this solution if it provided a solution to your question.

Shanker V

[ShankerV]

Hi @kiotsuresh 

Please check and let me know if my solution works.

Many thanks

Shanker V

[kiotsuresh]

Hi DenisZ,

The Entire Third row I need to derive from the other two rows, In You input there is a row added revenue, Below will be the input,

Item	month	Classification	Value	Category	Name
Chair	Jan-22	Furniture	50000	Home Furniture	Profit
Chair	Jan-22	Furniture	50	Home Furniture	Sales

and Output

Item	month	Classification	Value	Category	Name
Chair	Jan-22	Furniture	50000	Home Furniture	Profit
Chair	Jan-22	Furniture	50	Home Furniture	Sales
Chair	Jan-22	Furniture	1000 = 50000/50	Home Furniture	Revenue

[kiotsuresh]

Hi Shanker,

Can you please attach the workflow

thanks

Sureshkumar

[ShankerV]

@kiotsuresh 

Please find the below input and output, where the 3rd row is completely derived.

Output:

Many thanks

Shanker V

[ShankerV]

@kiotsuresh 

Due to system limitations, I could not attached the workflow. Sorry for the inconvenience.

Can you please mirror my workflow, I have explained in 8 steps in detail.

Many thanks

Shanker V

[kiotsuresh]

@ShankerV 

I'm getting an error while using the mod Function. 

Formula used : if ((mod[RecordID],2)=1)) then [value]/[Row+1:value]
else 0
endif

[ShankerV]

Hi @kiotsuresh 

0 is 'Zero'

i.e in the statement else 0

Many thanks

Shanker V

[ShankerV]

@kiotsuresh 

If you have your workflow, please do attached along with the input.

I can troubleshoot and make it run.

Its becoz of the incoming dataset here you would have created RecordID as String instead of Integer.

Many thanks

Shanker V

[kiotsuresh]

Solution Achieved through different steps. Used Transpose and Crosstab functions and achieved.