Add new row if missing from format

[Minh]

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: 

 

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:

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

Than you.

[apathetichell]

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 · Accepted solution]

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. 

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

Dan