Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
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
Hi,
You have to add OrderId in applymap script. Something like below,
ApplyMap('Map',OrderId, 0)*Quantity as Result
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);
Hi Ananth
Replace Id with OrderId. As simple is that.
ApplyMap('Map',OrderId)*Quantity as Result
Hope that works.
Regards
Aviral Nag
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);
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...
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
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.