topic Re: Want to update record from another table where null in QlikView

Want to update record from another table where null

rishikeshtiwari — Tue, 20 Jan 2015 12:15:22 GMT

Hi All,

I have two tables. In first table some fields are blank.In second table those fields are filled.

I want first table in which if any field is blanks then these values should fill in second table.

I am attachind data & result also.

Table 1:

ProductNumber	ProductName	FiscalYear	Origin	Sales
1001	A	2000	IND	4000
1002	B	2001	AUS	5000
1003	C	2002		6000
1004	D	2003	USA	7000
1005	E			8000
1006	F			9000
1007	G	2006	BRA	10000
1008	H	2007	CHI	11000
1009	I	2008	RUSS	12000
1010	J	2009	JAP	13000

Table2:

ProductNumber	ProductName	FiscalYear	Origin	Sales
1001	A	2000	IND	4000
1002	B	2001	AUS	5000
1003	C	2002	FRA	6000
1004	D	2003	USA	7000
1005	E	2004	NUZ	8000
1006	F	2005	JER	9000
1007	G	2006	BRA	10000
1008	H			11000
1009	I			12000

Result is

ProductNumber	ProductName	FiscalYear	Origin	Sales
1001	A	2000	IND	4000
1002	B	2001	AUS	5000
1003	C	2002	FRA	6000
1004	D	2003	USA	7000
1005	E	2004	NUZ	8000
1006	F	2005	JER	9000
1007	G	2006	BRA	10000
1008	H	2007	CHI	11000
1009	I	2008	RUSS	12000

Re: Want to update record from another table where null

giakoum — Tue, 20 Jan 2015 12:30:11 GMT

left join the columns you need from the second table onto the first

load * from Table1;

left join (Table1)

load

ProductNumber, //the field to join on - add more if needed

FiscalYear as FiscalYearTable2,

Origin as OriginTable2

from Table2

And then use if statements to fill the null values on the full table in a new load :

load

....,

if isnull(FiscalYear), FiscalYearTable2, FiscalYear) as FiscalYear

etc.

then drop the fields you do not need any longer

drop FiscalYearTable2

etc.

This is not working code, it is for illustration purposes only

Re: Want to update record from another table where null

PrashantSangle — Tue, 20 Jan 2015 12:31:21 GMT

Hi,

Use outer join

Try like

final_table

load * from table1;

outer join

load * from table2;

Regards

Re: Want to update record from another table where null

its_anandrjs — Tue, 20 Jan 2015 12:33:53 GMT

Hi,

Try to join this tables

tmpTable1:

Load ProductNumber, ProductName, FiscalYear, Origin, Sales From Source;

Join

tmpTable2:

Load ProductNumber, ProductName, FiscalYear, Origin, Sales From Source;

NoConcatenate

FinalTab:

LOAD * Resident tmpTable1 Where Len(FiscalYear) > 0 and Len(Origin) > 0;

DROP Table tmpTable1;

Regards

Anand