topic Re: IF ERROR behavior during left join in a load in QlikView

IF ERROR behavior during left join in a load

Wed, 21 Sep 2016 22:22:41 GMT

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

Re: IF ERROR behavior during left join in a load

Gysbert_Wassenaar — Thu, 22 Sep 2016 15:53:39 GMT

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.

Re: IF ERROR behavior during left join in a load

Peter_Cammaert — Thu, 22 Sep 2016 15:55:43 GMT

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

Re: IF ERROR behavior during left join in a load

vishsaggi — Thu, 22 Sep 2016 16:00:44 GMT

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;

Re: IF ERROR behavior during left join in a load

miguelbraga — Thu, 22 Sep 2016 16:02:01 GMT

Hey there,

See the attached file

Best regards,

D.A. MB

Re: IF ERROR behavior during left join in a load

miguelbraga — Thu, 22 Sep 2016 16:02:52 GMT

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;

Re: IF ERROR behavior during left join in a load

Anonymous — Thu, 22 Sep 2016 16:03:44 GMT

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 *;

Re: IF ERROR behavior during left join in a load

el_aprendiz111 — Thu, 22 Sep 2016 16:21:11 GMT

hi,

Re: IF ERROR behavior during left join in a load

Sun, 02 Oct 2016 14:40:18 GMT

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

Thanks!