alteryx Community

# Alteryx Designer Desktop Discussions

## Calculating one column from other column

8 - Asteroid

I have Data Below

 sub item item Split Value Date Chair_4 Chair 790,000 12/31/2023 Chair_3 Chair 45,454 12/31/2023 Chair_2 Chair 777555 12/31/2023 Chair_1 Chair 565,666 12/31/2023 Chair_0 Chair 888,999 12/31/2023 Table_4 Table 790,000 12/31/2023 Table_3 Table 45,454 12/31/2023 Table_2 Table 777555 12/31/2023 Table_1 Table 565,666 12/31/2023 Table_0 Table 888,999 12/31/2023 Chair_4 Chair 790,000 1/1/2024 Chair_3 Chair 45,454 1/1/2024 Chair_2 Chair 777555 1/1/2024 Chair_1 Chair 565,666 1/1/2024 Chair_0 Chair 888,999 1/1/2024 Table_4 Table 790,000 1/1/2024 Table_3 Table 45,454 1/1/2024 Table_2 Table 777555 1/1/2024 Table_1 Table 565,666 1/1/2024 Table_0 Table 888,999 1/1/2024

I want to calculate the split(3rd Column) Based on the value column.

The Logic is :

For a specific period(31-12-2023) the calculation for chair_4 should be, 79000/(790,000 +45,454 +777555+565,666 +888,999), and for chair_3 it should be 45454/(790,000 +45,454 +777555+565,666 +888,999) and it should calculate in the same way for all the items.

Datatype:

Value:Numeric

Date:Date

Rest all String

10 REPLIES 10
17 - Castor

It can be achieved with the below steps.

Step 1: RecordID tool

Step 2: Sort tool

Step 3: Formula tool

Step 4: Select tool

Step 5: Multi-Row Formula tool

Step 6: Sort tool

Many thanks

Shanker V

17 - Castor

Please find the detailed steps if more assistance is needed to the above response.

Step 1: RecordID tool

Step 2: Sort tool

Name -> Ascending

RecordID -> Descending

Step 3: Formula tool

Output column: 31-Dec-23

ToString(ToNumber([31-Dec-23]))

Step 4: Select tool

Convert Type of column Split and 31-Dec-23 as Double

Step 5: Multi-Row Formula tool

Update Existing Field: Split

IF [item] = [Row-1:item]
THEN [31-Dec-23] + [Row-1:31-Dec-23]
ELSE [31-Dec-23]
ENDIF

Step 6: Sort tool

RecordID -> Ascending

Hope this helps!!!

Many thanks

Shanker V

8 - Asteroid

can you give it in the workflow please?

17 - Castor

Apologies, could not upload the workflow due to some error.

Please do replicate the above steps as it worked.

Many thanks

Shanker V

14 - Magnetar

@Alteryxexpert does the workflow attached help?

8 - Asteroid

column name will change for upcoming months, currently it is 31-Dec-2023, next it will be 01-Jan-2024 ,02-Feb-2024 and so on, and this calculation needs to be dynamic

8 - Asteroid

Also Is there a way to achieve the same formula using the below data which is before transform,

 sub item item Split Value Date Chair_4 Chair 790,000 12/31/2023 Chair_3 Chair 45,454 12/31/2023 Chair_2 Chair 777555 12/31/2023 Chair_1 Chair 565,666 12/31/2023 Chair_0 Chair 888,999 12/31/2023 Chair_4 Chair 790,000 1/1/2024 Chair_3 Chair 45,454 1/1/2024 Chair_2 Chair 777555 1/1/2024 Chair_1 Chair 565,666 1/1/2024 Chair_0 Chair 888,999 1/1/2024

if this is achievable then the workflow will work dynamically

14 - Magnetar

@Alteryxexpert this might help with the pre change

8 - Asteroid

Values are numeric here, in your workflow it is string, I can't use replace on top of a numeric field also can't convert to str

Labels