Adding two tables. Do not know how to approach. Help plz.

[#1]

I have the following data.

Table 1:

State	Channel	Packing	Product	Value1
State1	Dealer	Bag	Product1	100
State1	Direct	Bulk	Product2	100
State1	Direct	Bag	Product2	100
State2	Dealer	Bag	Product1	200
State2	Direct	Bulk	Product2	200

Table 2:

State	Channel	Packing	Product	Value2
State1	Dealer	Bag	Product1	10
State1	Direct	Bulk	Product2	20

For all the remaining combinations of Table 1 -> Value2 = 50

I need to add Value2 to Value1 and get Table 3 as shown below.

Table 3:

State	Channel	Packing	Product	Value1	Value2	Value1 + Value 2
State1	Dealer	Bag	Product1	100	10	110
State1	Direct	Bulk	Product2	100	20	120
State1	Direct	Bag	Product2	100	50	150
State2	Dealer	Bag	Product1	200	50	250
State2	Direct	Bulk	Product2	200	50	250

Unable to understand how to approach this.

Any help is very much appreciated.

Regards

[alexandros17]

Load all tables using noconcatenate instruction Ex.:

Table1:

noconcatenate

Load * from ....

Then write the following code:

TMP:

noconcatenate

Load State, Channel, Packing, Product, Value1 resident Table1;

left join Load * resident Table2;

FinalTable:

noconcatenate

Load

State,

Channel,

Packing,

Product,

Value1,

if(isnull(Value2, 50, Value2) as Value2,

if(isnull(Value2, Value1 + 50, Value1 + Value2) as SumV1_V2

resident TMP;

Drop table TMP;

Let me know ...

[ashfaq_haseeb]

Hi,

May be like this.

Regards

ASHFAQ

[#4]

Try this script:

table1:

LOAD * INLINE [

    State, Channel, Packing, Product, Value1

    State1, Dealer, Bag, Product1, 100

    State1, Direct, Bulk, Product2, 100

    State1, Direct, Bag, Product2, 100

    State2, Dealer, Bag, Product1, 200

    State2, Direct, Bulk, Product2, 200

];

left join (table1)

LOAD * INLINE [

    State, Channel, Packing, Product, Value2

    State1, Dealer, Bag, Product1, 10

    State1, Direct, Bulk, Product2, 20

];

table3:

load  *, Value1+Value2 as Value3  Resident table1 Where Exists (Value2);

load  State, Channel, Packing, Product, Value1, 50 as Value2, Value1+50 as Value3  Resident table1 Where not Exists (Value2);

drop Table table1;

[tresesco]

This?

[MarcoWedel]

Hi,

given these tables:

table1:
LOAD * FROM [http://community.qlik.com/thread/118846] (html, codepage is 1252, embedded labels, table is @1);

table2:
LOAD * FROM [http://community.qlik.com/thread/118846] (html, codepage is 1252, embedded labels, table is @2);
LOAD State, Channel, Packing, Product, 50 as Value2
FROM [http://community.qlik.com/thread/118846] (html, codepage is 1252, embedded labels, table is @1,
filters(Remove(Row, Pos(Top, 3)), Remove(Row, Pos(Top, 2))));

you could create table3 like:

NoConcatenate
table3:
LOAD * Resident table1;
Left Join (table3)
LOAD * Resident table2;
Left Join (table3)
LOAD *, Value1+Value2 Resident table3;

hope this helps

regards

Marco