Cross Join Data

[jagjit_singh]

Hi All,

I have two tables one which holds the field captions and the other table holds the data. Our property system only creates caption records if data is entered for the field else there is no record. Hence every caption will not have a data record. For the purpose of the reporting we want all the captions for every property code and if there is no data, a null value is okay.

I used the join multiple tool to join both the tables and as shown below the code is blank and not captions rows are appearing. Also after joining both the tables i like to transform the data to show the captions as headings and the data for each code.

 

I have attached the sample workflow and if you can assist please.

Thanks

Jag

[patrick_digan · Accepted solution]

Hi @jagjit_singh , see if the attached works for you. The keys were: 1) Append the list of codes to the caption file in order to get your 234 records and 2) Join and then Union with the data to make sure all 234 records come through.