Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ...
Hi,
May be like this.
Regards
ASHFAQ
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;
This?
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