IF ERROR behavior during left join in a load

[#1]

‌Hi all, I would like to know if it is possible to fill a field during a join with a specific value when no correspondence is found.

example:

table1 has:

key / value

1 / a

2 / b

3 / b

4 / c

5 / d

table2 has:

value / category

a / beer

c / soda

in a simple left join the result would be

1 / a / beer

2 / b /

3 / b /

4 / c / soda

5 / d /

how can I fill the empty lines where no correspondence was found in table2 with something and produce a result like

1 / a / beer

2 / b / water

3 / b / water

4 / c / soda

5 / d / water

???

tks!!!

[Gysbert_Wassenaar · Accepted solution]

In this case a mapping table will work:

mapT2:

MAPPING LOAD * INLINE [

Key, Cat

a, beer

c, soda

];

Table1:

LOAD Key, Value, ApplyMap('mapT2',Value, 'water') as Category

INLINE [

Key, Value

1, a

2, b

3, b

4, c

5, d

];

But often you need to do that in two steps. First join the two tables to create a new temporary table. Then create yet another table by loading the data from the temporary table and adding the missing values. Finally drop the temporary table.

[Peter_Cammaert]

Load the resulting table of your JOIN operation into a new resident table (watch out for an autoconcatenate) that replaces all NULL values with your defaults. A JOIN cannot do this by itself.

Best,

Peter

[vishsaggi]

Try this: As suggested by Gysbert

Table1:

LOAD * INLINE [

key, value

1 , a

2 , b

3 , b

4 , c

5 , d

];

LEFT JOIN (Table1)

Table2:

LOAD * INLINE [

value, category

a, beer

c, soda

];

FINAL:

LOAD key, value,

     IF(Len(category) = 0, 'Water', category) AS Category1

Resident Table1;

Drop Table Table1;

[miguelbraga]

Hey there,

See the attached file

Best regards,

D.A. MB

[miguelbraga]

The script:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Table:

LOAD * INLINE [

    key, value

    1, a

    2, b

    3, b

    4, c

    5, d 

];

left join

LOAD * INLINE [

    value, category

    a, beer

    c, soda

];

Temp:

NoConcatenate

LOAD key,

     if(isnull(category), 'water', category) as category,

     value

Resident Table;

Drop Table Table;

[Anonymous]

NULLASVALUE 'category';
Set NullValue = 'water';

TEMP:
LOAD * INLINE [
key, value
1, a
2, b
3, b
4, c
5, d
];

left join

LOAD * INLINE [
value, category
a, beer
b, soda
];

NoConcatenate

TABLE:
LOAD * Resident TEMP;
DROP Table TEMP;

NULLASNULL *;

[el_aprendiz111]

hi,

[#9]

Thank you all very much guys, different answers, all apply, learned from you all!

Thanks!