Issue with Multiple Overlapping Joins

[#1]

I'm new to qlikview and have found myself stumped in one of my projects.

I'm trying to join 3 tables that include a list of names and data recorded by month.  I've included and example below.  The issue I'm having is that the list of names aren't the same every month and when Smith is skipped in TableB it creates a null value in Apr that prevents the record in TableC from being joined to Table A.  This creates an unwanted duplicate record for Smith.  Is there a way to prevent this?

TableA:

LOAD * INLINE [

    Name, Jan, Feb, Mar

    Abe, 2, 3, 5

    Smith, 5, 2, 6

    Joe, 3, 2, 4

    Sal, 4, 3, 1

];

TableB:

Outer Join (TableA)

LOAD * INLINE [

    Name, Feb, Mar, Apr

    Abe, 3, 5, 4

    Joe, 2, 4, 4

    Sal, 3, 1, 3

];

TableC:

Outer Join (TableA)

LOAD * INLINE [

    Name, Mar, Apr, May

    Abe, 5, 4, 1

    Smith, 6, 3, 3

    Joe, 4, 4, 4

];

[#2]

QlikView will use fields that are common to both tables to join the tables so in the second join three columns will be used, Name, Mar and Apr. Since a value is missing (Smith, 6, 3 does not match Smith, 6, null) an new row is added. Perhaps you want something like this:

TableA:

LOAD * INLINE [

    Name, Jan, Feb, Mar

    Abe, 2, 3, 5

    Smith, 5, 2, 6

    Joe, 3, 2, 4

    Sal, 4, 3, 1

];

TableB:

Outer Join (TableA)

LOAD * INLINE [

    Name, Apr

    Abe, 4

    Joe, 4

    Sal, 3

];

TableC:

Outer Join (TableA)

LOAD * INLINE [

    Name, May

    Abe, 1

    Smith, 3

    Joe, 4

];

[#3]

I would but there's a problem when new names are added to the list.  If I add a name in TableB and only add Apr to TableA I'll be missing the data for Feb and Mar.  Let me add Mary to the script so you can see what I mean.

TableA:

LOAD * INLINE [

    Name, Jan, Feb, Mar

    Abe, 2, 3, 5

    Smith, 5, 2, 6

    Joe, 3, 2, 4

    Sal, 4, 3, 1

];

TableB:

Outer Join (TableA)

LOAD * INLINE [

    Name, Feb, Mar, Apr

    Abe, 3, 5, 4

    Joe, 2, 4, 4

    Sal, 3, 1, 3

    Mary, 4, 1, 2

];

TableC:

Outer Join (TableA)

LOAD * INLINE [

    Name, Mar, Apr, May

    Abe, 5, 4, 1

    Smith, 6, 3, 3

    Joe, 4, 4, 4

    Mary, 1, 2, 3

];

[#4]

In that case simply load the name and Jan for the first load and just the name and first month for each join.

[#5]

I'd still have the problem with nulls popping up and messing up the joins if I did it that way.  Is there no way to remove the nulls or replace them somehow?

[#6]

Please check the attachment. Does it work for you?

Kiran.

[#7]

That works, but is there a way to return a null instead of a zero when all values are null?

[#8]

Updated accordingly.

Kiran

[#9]

The expressions don't work but I think it's because there's more then just numbers and nulls in the original list.  There's also '...' and 'N/A' to designate certain types of nulls.  I could make a bigger if statement to account for that, but on further thought I'm just going to chop a couple months off of the second load so joining the third table works correctly.  I lose a little bit of data in the short term, but in the long term it won't matter.

Thanks for all the help.