topic Re: Applymap() error.Please help me. in QlikView

Applymap() error.Please help me.

Fri, 17 Jul 2015 16:57:48 GMT

I am getting an error saying <proprty country> not found.Am I doing anything wrong here ?

Countryreference:

Mapping LOAD

Name as [Property Country],

abbr as CountryAbbrev

FROM [..\data\World Country Reference.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

buildingsTemp:

LOAD

[Building ID],// AS %BuildingKey,

If([Building Country]='USA', 'United States', [Building Country]) AS [Property Country],

ApplyMap('Coutryreference',[Property Country]) as CountryAbbrev

FROM

[..\data\Building Data.xls]

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

Re: Applymap() error.Please help me.

sunny_talwar — Fri, 17 Jul 2015 17:02:32 GMT

You need to put the name of field that you are using to map in your Countryreference table. Is the field called Porperty Country in buildingsTemp??? If not whatever it is called use that instead of [Property Country]

ApplyMap('Coutryreference',[Property Country]) as CountryAbbrev

May be this:

ApplyMap('Coutryreference',[Building Country]) as CountryAbbrev

Re: Applymap() error.Please help me.

Oleg_Troyansky — Fri, 17 Jul 2015 17:02:32 GMT

In your load, the field {Property Country] is not yet available - you are calculating it there. You can either repeat the same IF() function within the ApplyMap, or use a preceding load for simplicity, like this:

Countryreference:

Mapping LOAD

Name as [Property Country],

abbr as CountryAbbrev

FROM [..\data\World Country Reference.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

buildingsTemp:

LOAD

ApplyMap('Coutryreference',[Property Country]) as CountryAbbrev

;

LOAD

[Building ID],// AS %BuildingKey,

If([Building Country]='USA', 'United States', [Building Country]) AS [Property Country]

FROM

[..\data\Building Data.xls]

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

Re: Applymap() error.Please help me.

Fri, 17 Jul 2015 17:07:14 GMT

I have 2 fields in reference table Name and Abbr.

In main table Building country is the field.Which is modified to

If([Building Country]='USA', 'United States', [Building Country]) AS [Property Country],

Because of the data issue.

Re: Applymap() error.Please help me.

Fri, 17 Jul 2015 17:20:57 GMT

how do I put the if() within the applymap()

Thank you very much

Re: Applymap() error.Please help me.

sunny_talwar — Fri, 17 Jul 2015 17:23:15 GMT

May be this:

Countryreference:

Mapping LOAD

Name as [Property Country],

abbr as CountryAbbrev

FROM [..\data\World Country Reference.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

buildingsTemp:

LOAD

[Building ID],// AS %BuildingKey,

If([Building Country]='USA', 'United States', [Building Country]) AS [Property Country],

ApplyMap('Coutryreference', If([Building Country]='USA', 'United States', [Building Country])) as CountryAbbrev

FROM

[..\data\Building Data.xls]

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

Re: Applymap() error.Please help me.

sasiparupudi1 — Fri, 17 Jul 2015 17:33:50 GMT

You can not refer to an expression field in another expression in the load statement.

You can do a preceeding load

Countryreference:

Mapping LOAD

Name as [Property Country],

abbr as CountryAbbrev

FROM [..\data\World Country Reference.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

buildingsTemp:

LOAD

[Building ID],

ApplyMap('Coutryreference',[Property Country]) as CountryAbbrev;

LOAD

[Building ID],// AS %BuildingKey,

If([Building Country]='USA', 'United States', [Building Country]) AS [Property Country]

//ApplyMap('Coutryreference',[Property Country]) as CountryAbbrev

FROM

[..\data\Building Data.xls]

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

or use the expression directly in the apply map

ApplyMap('Coutryreference',If([Building Country]='USA', 'United States', [Building Country]));