I have two tables similar to the below sample table,
The above table is for particular file format (X) and I have another table similarly for another format (Y). I'm having three carriers namely Cargosmart, Hapag Lloyd and Maersk for both X and Y formats. I have the below two requirements in this case,
1. I want to compare the field names (field names can be similar or different in data source for each formats) based on the carriers and get the reused field count. i.e (if the field name is same for two carriers, it is taken into count as reused fields).
2. I also want to compare the field names based on some conditions/columns in the data (Qualifier/Enumeration, Data type, Min Occurs, Max Occurs, Length, M/C/O) i.e (if the field name is same for two carriers, it should check all these conditions and if any one of the conditions varies it should not be included into the count).
Help me guys on how to design a data model for these two scenarios and I've attached the data also.