ApplyMap not working

[cclayford]

Hi all

Please see my script bellow, and advise where i am going wrong:

TAB 1:

"MAP REGIONS":

MAPPING

LOAD Region_ID,

     Region_Name;

LOAD Region_ID,

     Region_Name

FROM

(biff, embedded labels, table is Regions$);

"MAP STATIONS":

MAPPING

LOAD Station_ID,

     Station_Name;

LOAD Station_ID,

     Station_Name

FROM

(biff, embedded labels, table is Stations$);

"MAP SHOPS":

MAPPING

LOAD Shop_ID,

     Shop_Name;

LOAD Shop_ID,

     Shop_Name

FROM

(biff, embedded labels, table is Shops$);

TAB 2:

"ACCOUNTS":

LOAD

     Year,

     Month,

     YYYYMM,

     applymap('MAP REGIONS',Region_ID) as Region,

     applymap('MAP STATIONS',Station_ID) as Station,

     [Customer Category],

     applymap('MAP SHOPS',Shop_ID) as Shop,

     [Previous Date],

     [Current Date2],

     [Difference (Days)],

     [Notified Demand],

     [Previous Reading Peak],

     [Current Reading Peak2],

     [Consumption Peak],

     [Previous Reading Standard],

     [Current Reading Standard2],

     [Consumption Standard],

     [Previous Reading Off-Peak],

     [Current Reading Off-peak2],

     [Consumption Off-Peak],

     [Previous Flat Rate],

     [Current Flat Rate],

     [Consumption Flate Rate],

     [Total Consumption],

     Tariff,

     [Service Charge],

     [Administration Charge],

     [Electrification and Rural subsidy],

     [Enviromental Levy],

     [Peak (High)],

     [Standard (High)],

     [Off-Peak (High)],

     [Peak (Low)],

     [Standard (Low)],

     [Off-Peak (Low)],

     [Network Access Charge],

     [Flat Rate],

     Account,

     [Eskom Account],

     latitude,

     longitude

FROM

(biff, embedded labels, table is Accounts$);

TAB 3:

"METERS":

LOAD

     applymap('MAP REGIONS',Region_ID) as Region,

     applymap('MAP STATIONS',Station_ID) as Station, 

     [Customer Category],

     applymap('MAP SHOPS',Shop_ID) as Shop, 

     [Meter Number],

     [Meter Type],

     [Firmware version],

     [Installation Date],

     [Installation Time (24h)],

     [CT Ratio],

     [Circuit Breaker Size (A)],

     [Distribution Board],

     [Modem Serial No.],

     [Cell Number],

     [IP Address (Internal)],

     Port,

     Network,

     [Antenna Type],

     [Seal Number],

     [GPS coordinates]

FROM

(biff, embedded labels, table is Sheet1$);

Thanks!

[#2]

Hi

check the mapping load statement

ur using 2 load statements

check this

[cclayford]

I Have changed my Mapping loads to this:

"MAP REGIONS":

MAPPING

LOAD Region_ID,

     Region_Name

FROM

(biff, embedded labels, table is Regions$);

"MAP STATIONS":

MAPPING

LOAD Station_ID,

     Station_Name

FROM

(biff, embedded labels, table is Stations$);

"MAP SHOPS":

MAPPING

LOAD Shop_ID,

     Shop_Name

FROM

(biff, embedded labels, table is Shops$);

But i still get this error...

[#4 · Accepted solution]

Hi

first u load the entire table

in ur load using region_id,Region_name fields but these are not loaded

for this first u have to load Prasa lookup data.xls table

after that u create the mapping load by using resident load

[AbhijitBansode]

make sure that you have named all the fileds correctly in load statements within the script.

Regards,

Sparten B

[#6]

Hi,

You are getting thi error because you dont have the Region_ID field in this file

""

Thanks

Fahad

[#7]

Dear Christopher.

to apply the ApplyMap two tables must have common column.

Mapping table and Main table.

Like

Region_ID ,Station_ID

these two filed's are available in Mapping Table and it should be available in Main table

( Where you are using pplyMap)

Thanks,

Mukram

[cclayford]

Hi all

Thank you very much for all your help.

I have used your suggestions and have fixt my ApplyMap.

Here is my revised script:

TAB 1 (LOOKUPS):

"REGIONS":

LOAD Region_ID,

     Region_Name

FROM

(biff, embedded labels, table is Regions$);

"STATIONS":

LOAD Station_ID,

     Station_Name

FROM

(biff, embedded labels, table is Stations$);

"SHOPS":

LOAD Shop_ID,

     Shop_Name

FROM

(biff, embedded labels, table is Shops$);

TAB 2 (MAPPING TABLES):

"MAP REGIONS":

MAPPING

LOAD Region_ID,

     Region_Name 

resident "REGIONS";

"MAP STATIONS":

MAPPING

LOAD Station_ID,

     Station_Name

resident "STATIONS";

"MAP SHOPS":

MAPPING

LOAD Shop_ID,

     Shop_Name

resident "SHOPS";

TAB 3 (ACCOUNTS):

"ACCOUNTS":

LOAD

     Year,

     Month,

     YYYYMM,

     applymap('MAP REGIONS',Region_ID) as Region,

     applymap('MAP STATIONS',Station_ID) as Station,

     [Customer Category],

     applymap('MAP SHOPS',Shop_ID) as Shop,

     [Previous Date],

     [Current Date2],

     [Difference (Days)],

     [Notified Demand],

     [Previous Reading Peak],

     [Current Reading Peak2],

     [Consumption Peak],

     [Previous Reading Standard],

     [Current Reading Standard2],

     [Consumption Standard],

     [Previous Reading Off-Peak],

     [Current Reading Off-peak2],

     [Consumption Off-Peak],

     [Previous Flat Rate],

     [Current Flat Rate],

     [Consumption Flate Rate],

     [Total Consumption],

     Tariff,

     [Service Charge],

     [Administration Charge],

     [Electrification and Rural subsidy],

     [Enviromental Levy],

     [Peak (High)],

     [Standard (High)],

     [Off-Peak (High)],

     [Peak (Low)],

     [Standard (Low)],

     [Off-Peak (Low)],

     [Network Access Charge],

     [Flat Rate],

     Account,

     [Eskom Account],

     latitude,

     longitude

FROM

(biff, embedded labels, table is Accounts$);

TAB 4: (METER DATA):

"METERS":

LOAD

     applymap('MAP REGIONS',Region_ID) as Region,

     applymap('MAP STATIONS',Station_ID) as Station, 

     [Customer Category],

     applymap('MAP SHOPS',Shop_ID) as Shop, 

     [Meter Number],

     [Meter Type],

     [Firmware version],

     [Installation Date],

     [Installation Time (24h)],

     [CT Ratio],

     [Circuit Breaker Size (A)],

     [Distribution Board],

     [Modem Serial No.],

     [Cell Number],

     [IP Address (Internal)],

     Port,

     Network,

     [Antenna Type],

     [Seal Number],

     [GPS coordinates]

FROM

(biff, embedded labels, table is Sheet1$);

Thanks once again