topic Re: Data mapping condition in script in QlikView

Data mapping condition in script

Fri, 29 Apr 2016 09:29:16 GMT

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

Re: Data mapping condition in script

Kushal_Chawda — Fri, 29 Apr 2016 09:41:18 GMT

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;

Re: Data mapping condition in script

avinashelite — Fri, 29 Apr 2016 09:41:43 GMT

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

Re: Data mapping condition in script

tamilarasu — Fri, 29 Apr 2016 09:49:20 GMT

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;

Re: Data mapping condition in script

jonathandienst — Fri, 29 Apr 2016 10:11:28 GMT

That is what I would so. Simple and effective.

Re: Data mapping condition in script

Fri, 29 Apr 2016 10:23:25 GMT

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;

Re: Data mapping condition in script

Fri, 29 Apr 2016 10:29:46 GMT

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

Re: Data mapping condition in script

Fri, 29 Apr 2016 10:37:39 GMT

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

Re: Data mapping condition in script

tamilarasu — Fri, 29 Apr 2016 11:17:12 GMT

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

Re: Data mapping condition in script

sunny_talwar — Fri, 29 Apr 2016 23:23:55 GMT

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;

Re: Data mapping condition in script

Mon, 02 May 2016 08:29:18 GMT

This is also slow down the reload task and consuming memory

Re: Data mapping condition in script

Mon, 02 May 2016 08:29:36 GMT

still the same issue

Re: Data mapping condition in script

sunny_talwar — Mon, 02 May 2016 08:31:24 GMT

Do you know which part (concatenation or the resident) is slowing down the reloading? and have you made comparison as to which method has done the best so far. May be we can choose one of the methods (most likely the best performing one) and look for ways to optimize it further

Re: Data mapping condition in script

Mon, 02 May 2016 08:39:03 GMT

issue is ate resident load

Re: Data mapping condition in script

sunny_talwar — Mon, 02 May 2016 08:41:43 GMT

Does it take substantially lesser time if you remove the where clause?

FinalTable:

NoConcatenate

LOAD ID,

Product,

Amount

Resident Table

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

Order By ID, Product, Amount desc;

DROP Table Table;

Re: Data mapping condition in script

Kushal_Chawda — Mon, 02 May 2016 09:24:45 GMT

Give it a try

Map:

mapping LOAD

Autonumber(ID&Product) as Key,

Amount

FROM Table2;

Data1:

LOAD ID,

Product,

Autonumber(ID&Product) as Key,

rangemax(applymap('Map',Autonumber(ID&Product)),Amount) as Amount

FROM Table1;

concatenate(Data1)

LOAD *

where not exists(Key);

LOAD ID,

Product,

Amount,

Autonumber(ID&Product) as Key

FROM Table2;

Drop Field Key;

Re: Data mapping condition in script

Mon, 02 May 2016 09:46:59 GMT

Yes without where it works good and even better without order by

Re: Data mapping condition in script

Mon, 02 May 2016 09:48:01 GMT

swuehl‌ can you suggest something?

Re: Data mapping condition in script

Tue, 03 May 2016 08:49:59 GMT

@stalwar1 any update.

Re: Data mapping condition in script

Tue, 03 May 2016 08:51:22 GMT

This looks ok