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

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

Mon, 19 May 2014 07:41:04 GMT

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

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

alexandros17 — Mon, 19 May 2014 07:53:03 GMT

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 ...

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

ashfaq_haseeb — Mon, 19 May 2014 07:59:12 GMT

Hi,

May be like this.

Regards

ASHFAQ

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

Mon, 19 May 2014 08:02:04 GMT

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;

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

tresB — Mon, 19 May 2014 08:06:22 GMT

This?

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

MarcoWedel — Mon, 19 May 2014 23:49:04 GMT

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