Merging two tables

[#1]

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?

[#2 · Accepted solution]

Something like this.

[#3]

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;

[#4]

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.

[#5]

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