topic Re: ApplyMap in QlikView

ApplyMap

Tue, 01 Jul 2014 05:10:56 GMT

Hi Qv,

I'm facing problem with apply map.

I have following script :

Directory;

Tab1:

LOAD Id,

Product,

Sales

FROM

E:\Ananth_Works\Applymap.xlsx

(ooxml, embedded labels, table is Tab1);

Map:

Mapping load

Id,

Sales

Resident Tab1;

Directory;

Tab2:

LOAD OrderId,

Orders,

Quantity,

ApplyMap('Map',Id)*Quantity as Result

FROM

E:\Ananth_Works\app2.xlsx

(ooxml, embedded labels, table is Sheet1);

When I run this script I'm getting following error :

Field not found - <Id>

Tab2:

LOAD OrderId,

Orders,

Quantity,

ApplyMap('Map',Id)*Quantity as Result

FROM

E:\Ananth_Works\app2.xlsx

(ooxml, embedded labels, table is Sheet1)

Please help me whats wrong with this.

Thanks & Regards,

Ananth

Re: ApplyMap

its_anandrjs — Tue, 01 Jul 2014 05:18:52 GMT

Try like

If from table Applymap Product is not required then comment it and use same table instead to load resident table.

Map:

Mapping LOAD Id,

//Product,

Sales

FROM

E:\Ananth_Works\Applymap.xlsx

(ooxml, embedded labels, table is Tab1);

//Map:

//Mapping load

//Id,

//Sales

//Resident Tab1;

Tab2:

LOAD OrderId,

Orders,

Quantity,

ApplyMap('Map',Id)*Quantity as Result

FROM

E:\Ananth_Works\app2.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: ApplyMap

Tue, 01 Jul 2014 05:27:14 GMT

Thanks for your response anand,

I'm facing same error even after doing the same.

But I don't want to do like this.

Why resident load is not working and why this error "field not found"

please help

Re: ApplyMap

Anonymous — Tue, 01 Jul 2014 05:30:57 GMT

Hi,

You have to add OrderId in applymap script. Something like below,

ApplyMap('Map',OrderId, 0)*Quantity as Result

Re: ApplyMap

its_anandrjs — Tue, 01 Jul 2014 05:32:12 GMT

Oh you need to rename the field Id as OrderId thats why it is not found.

Map:

Mapping LOAD Id as OrderId,

//Product,

Sales

FROM

E:\Ananth_Works\Applymap.xlsx

(ooxml, embedded labels, table is Tab1);

Re: ApplyMap

aveeeeeee7en — Tue, 01 Jul 2014 05:33:56 GMT

Hi Ananth

Replace Id with OrderId. As simple is that.

ApplyMap('Map',OrderId)*Quantity as Result

Hope that works.

Regards

Aviral Nag

Re: ApplyMap

its_anandrjs — Tue, 01 Jul 2014 05:34:43 GMT

Your complete script is

Tab1:

LOAD Id as OrderId,

Product,

Sales

FROM

E:\Ananth_Works\Applymap.xlsx

(ooxml, embedded labels, table is Tab1);

Map:

Mapping load

OrderId,

Sales

Resident Tab1;

Drop table Tab1;

Tab2:

LOAD OrderId,

Orders,

Quantity,

ApplyMap('Map',OrderId)*Quantity as Result

FROM

E:\Ananth_Works\app2.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: ApplyMap

Tue, 01 Jul 2014 05:41:03 GMT

Thanks to one and all.

My issue has resolved.

Finally one thing I understood that, The key field should be same in both the tables right!

Correct me if i'm wrong...

Re: ApplyMap

Anonymous — Tue, 01 Jul 2014 06:02:17 GMT

Hi,

The naming convention of the key field is not compulsory to be same in both the tables. However, you should have matching data in both the tables to achieve the result.

You could have achieve the same result without renaming the field with below script,

ApplyMap('Map',OrderId)*Quantity as Result

Thanks

Re: ApplyMap

its_anandrjs — Tue, 01 Jul 2014 06:05:43 GMT

Yes right when do mapping the base key field name should be same in the tables

Ex:-

Map:

Mapping Load

OrderId,

Sales

From Location;

TableName:

Load

OrderId,

Quantity

ApplyMap('Map',OrderId) * Quantity as Result

From Location;

Also the Id in the both field should be same then apply map the table.

Re: ApplyMap

Anonymous — Tue, 01 Jul 2014 06:14:21 GMT

Hi Anand,

I think, you are misleading here. It is not compulsory that the key field name to be same in both table in apply map.

Thanks

Re: ApplyMap

its_anandrjs — Tue, 01 Jul 2014 06:19:16 GMT

In the base table basically the field should be same

Ex:-

Map:

Mapping LOAD * Inline

[

Id,Desc

a,fsdf

b,saf

c,dfdsa

];

LOAD BaseId,Word,ApplyMap('Map',BaseId) as Description;

LOAD * Inline

[

BaseId,Word

a,23fd

b,45fdsf

c,3fsdf

d,56fdsf

e,566fsdf

];

Re: ApplyMap

its_anandrjs — Tue, 01 Jul 2014 06:21:22 GMT

Correct i am suggest for the base table not for Mapping table see the example below suggested by me in that table field name should be same not in mapping table. If it is same ok but not necessary always but good practice is should be same for understanding.

Regards

Anand

Re: ApplyMap

Tue, 01 Jul 2014 06:23:11 GMT

I don't know, but I tried with my scenario it is giving same result in both the cases.

i.e

Map:

Mapping Load

Id,

Sales

From Location;

TableName:

Load

OrderId,

Quantity

ApplyMap('Map',OrderId) * Quantity as Result

From Location;

Thanks,

Ananth