Generate row function

Question

Hi,

I am working on replacing MS Access based process in an Alteryx workflow, in my final step I am trying to map a static table values to input data file wherever it matches. However I need one extra row to be created for each static record which is not present in my input file. How can I use Generate Row function, where can I write the query.

My Static table has 15 records, with primary key as name of those 15 officers and my input file contains monthly data of the incidents where primary key will be this officer name only. So if for a given month, there is no record present for the given officer, I need to insert one row with only 4 minimum attributes (including the primary key as officer name). How can I do that?

Unfortunately, I cant share the dataset here.

Thanks,

Kishore Karankal

kishorhk · Answer

Hello @BS_THE_ANALYST ,

Thanks for the details, I liked the idea for the union and left unjoin, however my Data file has 2 columns I need to populate before the static data records so that it can be counted.

My Data file has 19 records, while following your union and left / right join technique I am able to populate the unmatched static records below the Data records, however the unjoin also adds the unwanted records from static table towards the end.

So I need below,

The Static data value for Officer Name and Area to be populated along with 2 required columns from Data table. Out of below, Column DataSource value is appended from a text input in workflow earlier, similar the RunDate is another value appended from a static table which will be a rundate that will be changed with every run.

BS_THE_ANALYST · Answer

@kishorhk I think you could just do a Left Outer Join with the Static Table as the left table.

If there is no match for an officer, you will see null values. Alternatively, if you just want to see the Officers who didn't match to the data, you can click on the L anchor:

and then tag them with info: