topic Merging two tables in QlikView

Merging two tables

Thu, 15 Jul 2010 16:56:28 GMT

Hi,

I need a solution to this problem:

File1:
LOAD * INLINE
[
Name
Abel
Jackson
Peter
];

File2:
LOAD * INLINE
[
Name, Age
Abel, 36
Jackson, 46
];

File1 contains the names. File2 contains the names and the ages. In File1, Peter does not have a corresponding record in File2, so I don't know his age. In this example, I'd like to default Peter's age to 18 and still be able to display the three names as follows:

Name Age
Wilson 36
Jackson 46
Peter 18

The script above results in two tables and a key on the Name field. Putting the results on the designer shows all the names, except that Peter's age is null. I don't want to use an IF statement in the expression to default the name because then I can't use the default age as an option in a filter. I would like to add a listbox that contains the ages of these individuals to enable filtering. This means that the default value must somehow find its way to the table. How do I do this?

Merging two tables

Thu, 15 Jul 2010 18:35:20 GMT

Something like this.

Merging two tables

Thu, 15 Jul 2010 19:52:11 GMT

You can achieve this by joining the two tables together, then re-loading them accounting for nulls in the age field.

You'll need to drop your temp table afterwards

File1:
LOAD * INLINE
[
Name
Abel
Jackson
Peter
];

LEFT JOIN LOAD * INLINE
[
Name, Age
Abel, 36
Jackson, 46
];
SecondLoad:
LOAD
Name,
IF(isnull(Age), 18, Age) AS Age
RESIDENT File1;

Merging two tables

Fri, 16 Jul 2010 07:29:29 GMT

Awesome!! It works perfectly okay. What I need to do now is apply the script to the real problem that I have and I have no doubt that it will work perfectly there. My man! Thanks a lot.

Merging two tables

Fri, 16 Jul 2010 07:32:05 GMT

Works beautifully!! Thank you very much, you're the man.