Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

I have the following data.

Table 1:

StateChannelPackingProductValue1
State1DealerBagProduct1100
State1DirectBulkProduct2100
State1DirectBagProduct2100
State2DealerBagProduct1200
State2DirectBulkProduct2200

Table 2:

StateChannelPackingProductValue2
State1DealerBagProduct110
State1DirectBulkProduct220

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:

StateChannelPackingProductValue1Value2Value1 + Value 2
State1DealerBagProduct110010110
State1DirectBulkProduct210020120
State1DirectBagProduct210050150
State2DealerBagProduct120050250
State2DirectBulkProduct220050250

Unable to understand how to approach this.

Any help is very much appreciated.

Regards

5 Replies
alexandros17
Partner - Champion III
Partner - Champion III

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
Champion III
Champion III

Hi,

May be like this.

Regards

ASHFAQ

Not applicable
Author

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

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;

QlikCommunity_Thread_118846_Pic1.JPG.jpg

QlikCommunity_Thread_118846_Pic2.JPG.jpg

QlikCommunity_Thread_118846_Pic3.JPG.jpg

hope this helps

regards

Marco