Applymap() error.Please help me.

[#1]

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$);

[sunny_talwar]

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

[Oleg_Troyansky]

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$);

[#4]

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.

[#5]

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

Thank you very much

[sunny_talwar]

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$);

[sasiparupudi1]

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]));