Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

IF ERROR behavior during left join in a load

‌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!!!

1 Solution

Accepted Solutions

Re: IF ERROR behavior during left join in a load

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.


talk is cheap, supply exceeds demand
8 Replies

Re: IF ERROR behavior during left join in a load

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.


talk is cheap, supply exceeds demand

Re: IF ERROR behavior during left join in a load

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
Esteemed Contributor III

Re: IF ERROR behavior during left join in a load

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
Valued Contributor III

Re: IF ERROR behavior during left join in a load

Hey there,

See the attached file

Best regards,

D.A. MB

miguelbraga
Valued Contributor III

Re: IF ERROR behavior during left join in a load

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;

roharoha
Valued Contributor III

Re: IF ERROR behavior during left join in a load

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
Valued Contributor

Re: IF ERROR behavior during left join in a load

hi,

tbl.png

Not applicable

Re: IF ERROR behavior during left join in a load

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

Thanks!

Community Browser