Problem with Workflow

[CeesayN]

Hi everyone,

I am new to Alteryx and I am working through a workflow I am putting together. I have a problem where I want to be able to do the following:

• I want the workflow to look in the description line where there is an 8 digit number, then copy that across and call the new column Additional PO then create another column called PO 2 and copy across the contents of Purchase Order Number column and the newly created Additional PO column.

• I want the workflow to look in the amounts column and identify contras (e.g -100 and +100) and where there is, then check that department code, account code, business unit code, PO 2 and location code for both are the same. If this condition is met, then remove both entries else leave them.

I would appreciate your help and I have attached a sample data to help.

Kind regards

Nuha

[danilang]

Hi @CeesayN 

You have a few different types of 8 digit numbers in the data.   

1. the line starts with a yyyymmdd string

2. The 8 digit number is part of an adjustment record as in "P10-19 Tech Nominal Recodes Adjustment GBP ~ 11503920-0001-SETEL LTD -Network Support & Connectivity -Recode to 75807"

3.  There are also instances of the line ending in a 10 digit number

I think you're looking for case 2, but some clarification would be appreciated

Dan

[JosephSerpis]

Hi @CeesayN  I believe I mocked something up that give you the output you describe but please let me know if there are any issues?

[CeesayN]

Hi @danilang

Thanks for looking at my query.

I think my explanation was not very clear. What I want is the first instance of an 8 digit number but not beginning with a zero. 

Hope this clarifies what I am trying to achieve a bit more.

Thanks once again

[danilang · Accepted solution]

Hi @CeesayN 

Here's a macro based solution. 

The main workflow builds up the PO 2 field and passes the non empty ones to the macro

The macro loops through the input records and matches the negative amount of first to the remaining.  If there are no matches, the record is sent to the data output.  If there are, the ids of the first pair, the original and the first negative match, are removed from the input input and the rest of the data sent back to iterative input for another loop.

This macro based solution will handle cases where the original and reversing entries may have intervening rows between them.

Dan 

[CeesayN]

Hi @JS420,

Thank you so very much for putting this workflow together. It has given me an idea of how some of these things work but I am very new to Alteryx so please bear with me.

I might not have explained thoroughly what I want to achieve. The 8 digit rule does not apply where the Purchase Order Number column is populated. It is to select any an 8 digit number in the Line description column where the Purchase Order Number column is blank. The 8 digit number could be preceded by GBP ~ 12345678 etc in some instances, it is never at the beginning or end of the description but in others it is preceded by PO

Hope this helps clarify a bit more and thanks for taking your time to look at this, much appreciated.

Regards

[CeesayN]

Hi @danilang 

I think you are using a newer version of Alteryx than I am as the Remove Duplicates tool is not working on mine as below.

Regards

[danilang]

Hi @CeesayN 

It extracted properly when I tried it.  Did you move the main workflow to a different location?  With a different version, you wouldn't be able to open the main workflow either

Dan