Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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$);
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
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$);
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.
how do I put the if() within the applymap()
Thank you very much
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$);
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]));