Dealing with non-exisiting values in one of two tables

[#1]

Hello community,

I load two tables from a database and save each to a qvd. Both are connect through the ID-column name.

Table Master contains the master data meaning that I only want to deal with the IDs of table B (Slave) where they match with A. For that I used Where Exists(ID, ID) in the slave table.

The slave table contains measure values for each ID. If an ID of the master table is not present in the slave table I get wrong numbers when I do a distinct count to get the numbers for the MeasureType column.

See this screenshot for details: Note: Here I don't use "where exists" to show you the complete example.

The goal here is to deal with the non-existing IDs in the slave table (77, 88) so that "No measure" has a count of 3 instead 1.

Would this work with joining only a subset of the columns from both tables or which way do I have to go here?

I included my sample qvw so you can have a look.

Thank you,

Thorsten

[Gysbert_Wassenaar · Accepted solution]

Try this:

Master:
LOAD * INLINE [
    ID, Column 1, Column 2, Column 3
    1, (1)col1, (1)col2, (1)col3
    77, (77)col1, (77)col2, (77)col3
    88, (88)col1, (88)col2, (88)col3
];

Slave:
LOAD *
,
if([Measure1] OR [Measure2]  ,'measure1+2',
if([Measure3] OR [Measure3] ,'measure3+4',
if(([Measure5]), 'measure5','NO measure')
)) AS MeasureType,
ID  as ID2
INLINE [
    ID, Measure1, Measure2, Measure3, Measure4, Measure5
    1, 12.34, , , 999.23,
    2,  , , 6.24, 888.79
    3, , 8.37, 6.24, 888.79
    4, , , , , 2
    5, , , , , ,
]    
Where Exists(ID, ID);

Concatenate(Slave)
load ID, 'NO measure' as MeasureType
resident Master
where not exists(ID2,ID);

drop field ID2;

[#3]

Perfect. This does the job. Thank you very much.