Data mapping condition in script

[#1]

Lets say I have two tables looks like below

Table 1

ID	Product	Amount
1	A	100
1	B	200

Table2

ID	Product	Amount
1	A	300
1	C	250

Now I want to create the Final table with below condition

If ID & Product from Table1 exist in Table2 , then Take the Amount wherever it is greater, in this case it will pick from table2.

Also take ID & Product from Table2 which not there in Table1.

Final Output

ID	Product	Amount
1	A	300
1	B	200
1	C	250

[Kushal_Chawda]

try something like below

Data1:

LOAD ID,

     Product,

     Autonumber(ID&Product) as Key

     Amount

FROM table1;

Data2:

noconcatenate

LOAD ID,

     Product,

     Autonumber(ID&Product) as Key

     Amount as Amount1

FROM table2;

left join(Data1)

LOAD Key,

     Amount1

Resident Data2

where exists(Key);

concatenate(Data1)

LOAD *

Resident Data2

where not exists(Key);

drop table Data2;

Final:

noconcatenate

LOAD *,

     if(Amount1>Amount,Amount1,Amount) as FinalAmount

Resident Data1;

drop table Data1;

[avinashelite]

try like this

Temp:

Mapping LOAD ID&'-'&Product as Key,

Amount

from Table 1;

Result:

LOAD ID&'-'&Product as Master_Key,

ID,

Product ,

if(Applymap('Temp',ID&'-'&Product,Amount)>Amount, Amount, Applymap('Temp',ID&'-'&Product,Amount) as Amount

from Table  2;

LOAD ID,

Product,

Amount

from Table 2

where not exist(Master_Key,ID&'-'&Product);

[tamilarasu]

You can also try,

A:
LOAD ID,
    Product,
    Amount
FROM Table1; 

Concatenate 

LOAD ID,
    Product,
    Amount
FROM Table2;

Inner Join (A)
Load ID,
Product,
Max(Amount) as Amount
Resident A Group by ID, Product;

[jonathandienst]

That is what I would so. Simple and effective.

[#6]

Hi,

I've attached script where after you loaded the 2 data tables, you can use the create the following table to show the desired result

NoConcatenate

Result:

Load

  ID,

  Product,

  Amount,

  autonumber(RecNo() , Product)

Resident

  Table

Where

  autonumber(RecNo() , Product) = 1

Order by

  Product, Amount desc;

[#7]

Don't you think that if amount of data is huge in this case max with group may cause the performance issue?

[#8]

This group by method consuming more RAM as compared to other's solution as amount of data is huge.

[tamilarasu]

Fine. Check the below script.

A:
LOAD ID,
     Product,
     Amount
FROM
Table1;

Concatenate

LOAD ID,
     Product,
     Amount
FROM
Table2;

Store A into A.qvd (qvd);
DROP Table A;

Data:
LOAD ID,
     Product,
     FirstSortedValue(Amount,-Amount) as Amount
FROM
A.qvd
(qvd) Group By ID, Product

[sunny_talwar]

Yet another option to test out:

Table:

LOAD ID,

    Product,

    Amount

FROM

[https://community.qlik.com/thread/215061]

(html, codepage is 1252, embedded labels, table is @1);

Concatenate(Table)

LOAD ID,

    Product,

    Amount

FROM

[https://community.qlik.com/thread/215061]

(html, codepage is 1252, embedded labels, table is @2);

FinalTable:

NoConcatenate

LOAD ID,

  Product,

  Amount

Resident Table

Where ID&Product <> Previous(ID&Product)

Order By ID, Product, Amount desc;

DROP Table Table;