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

[sunny_talwar]

I guess the issue is that in order to grab the Max from the two tables, you will somehow need to compare them and use some kind of aggregation to pick the max. I guess combining is not that resource intensify, but in your case Aggregation is an causing an issue. Alternatives? May be a lot, but the most efficient? very difficult to say.

I will propose one other way and we can check if that is helpful.

Best,

Sunny

[#3]

Can you please share?

[sunny_talwar]

Here is one another way to do it. My goal was to fasten up the aggregation process, but in the process, I might have slowed down other aspects. So once again, I don't know if this would improve anything, but no harm in giving it a try

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);

Temp:

LOAD 0 as Dummy

AutoGenerate 0;

FOR i = 1 to FieldValueCount('Product')

  LET vID = FieldValue('Product', $(i));

  Temp2:

  LOAD Product as Temp_Product,

  Amount as Temp_Amount

  Resident Table

  Where Product = '$(vID)';

  Concatenate(Temp)

  LOAD Max(Amount) as Amount,

  '$(vID)' as Product;

  LOAD FieldValue('Temp_Amount', RecNo()) as Amount

  AutoGenerate FieldValueCount('Temp_Amount');

  DROP Table Temp2;

NEXT

DROP Field Dummy;

Right Join (Table)

LOAD *

Resident Temp;

DROP Table Temp;

[#5]

Hi,

I think for loop will not be the good option.

[sunny_talwar]

Did you give it a try?

[#7]

For lesser data What Tamil has suggested works well and also simple to understand but for huge data I preferred kush suggestion.

[#8]

Yes. Working good but with respect to script readability I preferred without loop solution.

[tamilarasu]

I just want to confirm about the qvd load method. Did you try.? How about the loading time compared to other methods.? Thank you.

[#10]

Yes. It is Comparatively good but still consumes more memory with respect to other methods. My concern here is that I have limited memory and amount of data is huge so method I was looking for is to be like which consumes very less memory

[Kushal_Chawda]

Can you please share the comparison of all the methods with how much RAM consumed. with below details

1) Amount of data (No of rows)

2) RAM size

3) Application size

This comparison will definitely help others as optimization point.