Not understanding NoConcatenate

[dmoon2208]

Hi, I have spent several days reading all the posts and articles on NoConcatenatete and Data Modelling but I am still missing some key concepts. I have three tables:

Fire_DataSet: Date, Year, Month, Week of Year, Place of Fire (City, Rural), Type_of_Fire (0,1,2,3)

Police_DataSet: Date, Year, Month, Week of Year, Arrested (Y,N), Gender (M,F), postcode

Insurance_Claims_DataSet: Year, Week, Month, Gender (M,F), Sports-Related (Y,N), postcode

 I use the NoConcatenate in my load script. On a bar graph I can graph the totals in each dataset by month, year, week etc. The issue is when I select a filter for a dataset which is not in any of the other datasets such as Sports-Related. I want to filter the insurance claims but my totals for Fires and Police become 0 or if I chose City for Place_of_Fire the insurance claims and police totals become 0 which makes sense as they dont have that field but I thought  NoConcatenate would keep my tables separate and only link by the common fields so the totals which dont have that field are not affected. Would also like to filter on Postcode and have the Fire totals remain the same. Do I need to resort to doing set expressions or keep each table totally separate which means I wont be able to graph each total side by side? I have 20 of these tables each with multiple unique fields. Any assistance is appreciated.

[Vegar]

There are several levels to your question, but your understanding of NoConcatenate is correct. Using NoConcatenate forces the loaded table not to autoconcatenate into the bottom of any table loaded in prior to this.

In your case right three tables are not identical and will not concatenate it selves even without the NoConcatenate. The opposite to NoConcatenate load is Concatenate load, using concatenate load will force the data to concatenate into a table that already exist in the data model.

Autoconcatenate will only happen when you the to load a data set witch EXACTLY the same set of columns as a previously loaded table.

[Vegar · Accepted solution]

You mention that your data is not connected properly when selecting City values. My guess is that you do not have a 100% match on all the field combinations that makes up your synthetic key between the two tables. It could be that you have different dates, different format on month etc.

[Vegar · Accepted solution]

You could try to clean up the model a bit by adding a master calendar. Please look at my pseudo code below.

There are plenty of tips on how to create the master calendar in previous community postings.

Fire_DataSet:
Date,
Place of Fire (City, Rural),
Type_of_Fire (0,1,2,3)
From firedata;

Police_DataSet:
Date,
postcode,
Arrested (Y,N),
Gender (M,F)
From policedata ;

Insurance_Claims_DataSet:
Date,
postcode,
Gender (M,F),
Sports-Related (Y,N)
From insurancedata;

Calendar:
Load
% date,
Date,
Year,
Month,
Week of Year
FROM a generated master calendar;

[dmoon2208]

Thanks. I came to the same conclusion just before I saw your answer