topic Re: ApplyMap not working in QlikView

ApplyMap not working

cclayford — Fri, 16 Nov 2012 06:43:19 GMT

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!

Re: ApplyMap not working

Fri, 16 Nov 2012 06:47:37 GMT

check the mapping load statement

ur using 2 load statements

check this

Re: ApplyMap not working

cclayford — Fri, 16 Nov 2012 06:52:49 GMT

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...

Re: ApplyMap not working

Fri, 16 Nov 2012 07:02:04 GMT

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

Re: ApplyMap not working

AbhijitBansode — Fri, 16 Nov 2012 07:05:39 GMT

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

Regards,

Sparten B

Re: ApplyMap not working

Fri, 16 Nov 2012 07:12:19 GMT

Hi,

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

Thanks

Fahad

Re: ApplyMap not working

Fri, 16 Nov 2012 07:21:09 GMT

Dear Christopher.

to apply the ApplyMap two tables must have common column.

Mapping table and Main table.

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

Re: ApplyMap not working

cclayford — Fri, 16 Nov 2012 07:31:42 GMT

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