Duplicate Alteryx rows based on condition?

[Crispy131]

I have data that looks like the following table (with a few other columns). The problem is when I write an IF statement saying IF "Money" THEN [Value] ELSEIF "GP" THEN [Value] I don't get the correct value for GP as some of that value ended up falling under 'Money'. Same goes for if I used 'Costs' instead of Money.

Earning Profit    Sales    Value

ET	GP	Money	23
ET	GP	Costs	56
ET	GP	Costs	87
ET	GP	Money	42
ET	GP	Money	15
ET	Asset	Property	62
ET	Asset	Property	35

I want to use Alteryx to duplicate the Money rows and the Cost rows exactly as is but to make the Profit field blank as I'm hoping that will stop the IF condition issue. I have never done something like this before so not sure how to do it?

Really appreciate the help!

[K_Vinogradov]

Hi @Crispy131!

I believe the issue can be solved without duplicating the data. Could you send the exact formula you're using, or elaborate on the formula you would like to achieve?

In case you do believe data duplication is the only way to proceed, you can create a separate stream of data, use formula tool to set profit to blank, and combine it using the union tool. Just like this:


Hope this helps!

[aatalai]

Also take a look at the generate row tool

[Crispy131]

Hi @K_Vinogradov 

Thanks for your help. Below is the IF statement I want to use. I have also updated the table to show what it really looks like but I am only interested in the "ET", "GP", "Money" and "Costs" variables.

IF [Earning] = "ET" THEN "ET"

ELSEIF [Profit] = "GP" THEN "GP"

ELSEIF [Sales] = "Money" THEN "Money"

ELSEIF [Sales] = "Costs" THEN "Costs"

ENDIF

I then take the result of this IF and try and place the [Values] against each one (in Tableau). However although I get the value for ET I don't get any value for the other three because all those Values got added to ET.

I hope that makes sense :(

[binuacs]

@Crispy131 re arrange your if statement 

IF [Sales] = "Costs" THEN "Costs" 
ELSEIF [Sales] = "Money" THEN "Money"
ELSEIF [Profit] = "GP" THEN "GP"
ELSEIF [Earning] = "ET" THEN "ET"
ELSE NULL()
EMDIF

[Crispy131]

Hi @binuacs unfortunately that doesn't work..

[binuacs]

@Crispy131 can you provide the expected result based on your above input?

[Crispy131]

After the IF statement I get the four fields ET, GP, Money and Costs and the value for each should be as follows:

ET: 320

GP: 223

Costs: 143

Money: 80

Working out:

Sum of all the ET rows: 23+56+87+42+15+62+35=32023+56+87+42+15+62+35

Sum of GP rows: 23+56+87+42+15=22323+56+87+42+15

Sum of Costs rows: 56+87=14356+87

Sum of Money rows: 23+42+15=8023+42+15