how to combine missed data from two excel files

[#1]

Hi,

I have two excel files with the same fields

both of them contain this fields

ProductName , f1 , f2

the first one contain this data

ProductName , f1 , f2

A,  , 2

B, 3 ,

C,  , 6

D,  ,

and the second one contains this data

ProductName , f1 , f2

A, 1 ,

B,  , 4

C, 5 ,

D, 7 , 8

i want the result in qlikview to be one table with this data

ProductName , f1 , f2

A, 1 , 2

B, 3 , 4

C, 5 , 6

D, 7 , 8

So,how to take values from fields in table and put them in the empty places in another table??

Thanks in Advance.

[#2]

Do you want to sum the f1 and f2

[#3]

No,the null values in table1 i want to fill them from the values in table2 and vice versa

[#4]

Any Help??

[nagaiank · Accepted solution]

For the example data given by you, the following script gives the desired result:

T1:

LOAD * Inline [

ProductName , f1 , f2

A,,2

B,3,

C,,6

D,,

];

LOAD * Inline [

ProductName , f1 , f2

A,1,

B,,4

C,5,

D,7,8

];

Result:

LOAD ProductName, Sum(f1) as SumF1, Sum(f2) as SumF2 Resident T1 Group By ProductName;

[#6]

Thanks Alot!

But what if i want this one

the first one contain this data

ProductName , f1 , f2

A,  , cairo

B, lebanon ,

C,  , 6

D,  ,

and the second one contains this data

ProductName , f1 , f2

A, alex ,

B,  , USA

C, KSA ,

D, London , 8

i want the result in qlikview to be one table with this data

ProductName , f1 , f2

A, alex , cairo

B, lebanon , USA

C, KSA , 6

D, London  , 8

i mean if there's a text not an integer values??

[nagaiank]

If the fields f1 and f2 are texts (and not numbers), you may use the following script;

Result:

LOAD ProductName, Concat(f1,'') as SumF1, Concat(f2,'') as SumF2 Resident T1 Group By ProductName;

If the fields f1 and f2 are sometimes numbers and texts on other times, you need to handle the values in either case, the above will not work. (In that case I would change the data model rather than script.)

[#8]

Thanks so much for ur help