topic Issue with Multiple Overlapping Joins in QlikView

Issue with Multiple Overlapping Joins

Wed, 26 Oct 2011 00:01:05 GMT

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

];

Issue with Multiple Overlapping Joins

Wed, 26 Oct 2011 00:27:18 GMT

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

];

Issue with Multiple Overlapping Joins

Wed, 26 Oct 2011 00:36:29 GMT

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

];

Issue with Multiple Overlapping Joins

Wed, 26 Oct 2011 15:55:48 GMT

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

Issue with Multiple Overlapping Joins

Wed, 26 Oct 2011 16:35:31 GMT

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?

Re: Issue with Multiple Overlapping Joins

Wed, 26 Oct 2011 17:17:13 GMT

Please check the attachment. Does it work for you?

Kiran.

Re: Issue with Multiple Overlapping Joins

Wed, 26 Oct 2011 17:36:24 GMT

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

Re: Issue with Multiple Overlapping Joins

Wed, 26 Oct 2011 17:41:24 GMT

Updated accordingly.

Kiran

Re: Issue with Multiple Overlapping Joins

Wed, 26 Oct 2011 18:18:39 GMT

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.