Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
];
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
];
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
];
In that case simply load the name and Jan for the first load and just the name and first month for each join.
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?
Please check the attachment. Does it work for you?
Kiran.
That works, but is there a way to return a null instead of a zero when all values are null?
Updated accordingly.
Kiran
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.