ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

New User - Can someone kindly help with my problem

user011
5 - Atom

Hi, I am new to Alteryx and wondering if anyone could kindly help with my problem.

 

I receive data in a format where most entries are in order or simply need swapping around so that row AA, or CA is before row BB.

For these entries, they must be in the matching pairs with a blank row before the next pair. 

The amounts must also match; sometimes multiple BB's add up to one AA/CA in which case I break them up as shown in my example.

 

I am trying to use Alteryx to do the following:

 

1) Swap entries that are not in order around so that row AA, or CA is before row BB

2) Insert a blank row between each pair

3) Match all entries, even in the situation where multiple unmatched BB entries add up to one AA or CA entry

In the case of unmatched entries, I must count the amount of unmatched BB entries to identify which unmatched AA or CA entries they correspond with. I will then insert these as shown in bold, changing the amount of the first entry so that it matches the second, this does not change the total sum of amount. 

There can often be many of these unmatched entries so I am currently using trial and error when adding unmatched BB entries to find their corresponding match.

 

I understand there are a few challenges here, I have been trying to use Alteryx but as a new user have had no luck. If anyone can assist that would be greatly appreciated!

 

user011
5 - Atom

Example:

 

Some AA's or CA's can be made up of two BB's as shown in bold    
What I have    What I need  
          
SideRef 1AmountRef 2  SideRef 1AmountRef 2
AASXC777SWW  AASXC777SWW
BBSXD777SWE  BBSXD777SWE
BBEAA777CFE      
AAEAD777CWD  AAEAD777CWD
AADER477GEW  BBEAA777CFE
BBFAU122GER      
BBBWE355GRE  CAXEW9794DCW
CAXEW9794DCW  BBSEE9794DCW
BBSEE9794DCW      
      AADER122GEW
      BBFAU122GER
          
      AADER355GEW
      BBBWE355GRE

 

Labels