Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Issue with Multiple Overlapping Joins

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

];

8 Replies
Not applicable

Issue with Multiple Overlapping Joins

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

];

Not applicable

Issue with Multiple Overlapping Joins

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

];

Not applicable

Issue with Multiple Overlapping Joins

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

Not applicable

Issue with Multiple Overlapping Joins

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?

Not applicable

Re: Issue with Multiple Overlapping Joins

Please check the attachment. Does it work for you?

Kiran.

Not applicable

Re: Issue with Multiple Overlapping Joins

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

Not applicable

Re: Issue with Multiple Overlapping Joins

Updated accordingly.

Kiran

Not applicable

Re: Issue with Multiple Overlapping Joins

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.