ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

Add new row if missing from format

Minh
6 - Meteoroid

Hello,

 

I am struggling to find a way to add missing rows from the format to the final data set. I have 2 sets of data like below: 

 Q.PNGQ2.PNG

 

The first table contains sets of data and the second table contain the format each group must follow. If a row is missing from the group (For example, Input 3 is missing from group 3), the workflow will insert the missing row into the group (Insert Input 3 between Input 2 and 4 into group 3) and continue adding the missing rows until all of the groups has the correct format. This is my desired output:

A.PNG

Input 4 has been added to group 1 and 2. Input 3 has been added to group 3.

 

Than you.

apathetichell
11 - Bolide

revised - you don't need anything after the first crosstab if you set it to "first" instead of concatenate. it automatically fills in the blanks as null()

 

There's a few other ways of doing this one is fairly scalable. There' s also a transposed version to allow for easier joining with your other datasource.

danilang
17 - Castor
17 - Castor

Hi @Minh 

 

In these situations it's best to avoid crosstab tools if possible because the combination of crosstab/transpose replaces various characters with underscores.  You can try to clean them up afterwards, but if the original data contained underscores to begin with you're left with a messy problem.  There's also the problem of missing input data.  For example, if your template also contains Input 5 which is not included your input, it would never show up in the results by simply manipulating the input. 

 

danilang_0-1617882600242.png

The attached workflow uses the generate rows tool to expand your template so that it contains a full set of rows for each group listed in your data.  Join this to your original data, union the unmatched template records and sort, giving

danilang_1-1617882750040.png

 

Dan

Labels