Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Something like this.
Something like this.
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;
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.
Works beautifully!! Thank you very much, you're the man.