transformation and formula challenge

[pangersandmash]

Hello

I am stuck on a problem- wondering if anyone can help please.

I basically want to write a rule that says 'when an invoice has FTA, PUBINS, and PCCR, allocate the full amount to PUBINS'.

But, only for these 3. The screenshot below should help.

I tried to use a cross tab tool so i have something like the below

but i couldn't get any further. Any advice?

Thanks

[TheOC]

hey @pangersandmash 

Are you able to attach the excel 'before' table please?

Thanks!
TheOC

[pangersandmash]

This is it. An example of one invoice. There is a column that says 'product type'. It is the ADD ONS that i want to do this for.

Thank you

[TheOC]

hey @pangersandmash 

I believe to have done what you needed. I've tried to annotate formula where used.

Effectively i crosstab the data to this:

Then create a flag if FTA, PCCR and PUBINS are all not null.

If this flag is present, add the values to the PUBINS field, and null the other two:

I've gave you two outputs too - one as such:

And one in the format you shown:

Hopefully this helps!
TheOC

[TheOC]

Whoops, forgot to attach workflow!

Cheers,
TheOC

[pangersandmash]

Thank you! the second output looks ideal. What are the selections on the Cross tab/ transpose? Alternatively would you be able to please send the workflow?

Thanks

[TheOC]

hey @pangersandmash 

Workflow attached on second message - sorry i had forgotten on the first.

Let me know if it works for you!

Cheers,
TheOC

[pangersandmash]

I keep getting this message- it won't let me import into Alteryx ?!

[TheOC · Accepted solution]

hey @pangersandmash 

Try this one, you will have to change the input data.

Cheers,
TheOC

[pangersandmash]

works perfect. Thank you so much!