Not matching values in associated tables.

[MSZ]

Dears 

I need your help. I have two tables which I associated by creating key - this works fine. 

These are tables where in one table, I have daily data related to claims, second table contain data with monthly production. 

I need to combine data from both tables (as number of claims per number of manufactured products) 

However after association, in case of months where there were no claims it doesn't count number of production correctly (months where was no claims is not taken into the calculation). 

Here is example of mismatch

What would be your advice :

Set Analyzes ?

Master Calendar ?

Thanks 

Michal 

[stevedark]

Hi @MSZ 

This sounds like a job for a link table.

Load your Production table with your join field of Month and then your Claims table with the same.

You can then create a join table which has the superset of months from both tables, like this:

MonthLink:
LOAD DISTINCT
   Month
RESIDENT Production;

MonthLink:
LOAD DISTINCT
   Month
RESIDENT Claims;

This should then mean that if you have Month as a dimension that you can count Production rows and Claims and get values regardless of whether the month appears in both tables.

A better approach however may be not to join at all, and just concatenate your Claims onto the Production table. This way, if there are other fields that are common across the tables they will also appear in a single field that you can filter on.

To do this load your Production table first, then concatenate Claims like this:

CONCATENATE(Production)
LOAD
   Month,
   Year(Month) as Year,
   [Production Plant],
   etc.
... from wherever ...

The big advantage of this is that you can dispense of the QUALIFY statement and you don't have multiple fields with the same kind of content. Try and match as many fields as you can between the two tables, and any you can't match will have nulls where there is no match.

Either approach should work, but the second one may well work better.

Hope that helps.

Steve