Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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.

2 REPLIES 2
apathetichell
18 - Pollux

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
19 - Altair
19 - Altair

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