Hi,
Someone helped me write this script, so it's a bit beyond my capabilities. It is converting all income data whatever currency or distance measurement is used into USD and miles. For some reason it is loading my data multiple times and I don't know why.
conversionMap:
Mapping LOAD
AutoNumberHash128(Curnc_Year,Currency_Abbr,'currency') as ConversionKey,
USD_Equivalent
FROM [lib://Documents (1qconsulting_debi.cook)/Currency Conversion.xlsx]
(ooxml, embedded labels, table is Sheet1);
TempTable:
LOAD
YearID as TestYearID,
SubtextRow as Testsubtext,
[Year] as testyear,
if (SubtextRow = 'United Arab Emirates Dirham', 'AED', if(SubtextRow = 'Canadian Dollars', 'CAD')) as CurrencyAbbr
Resident [ResponseTable]
where match(SubtextRow, 'Canadian Dollars', 'United Arab Emirates Dirham')>0;
left Join ([ResponseTable])
Load
TestYearID as YearID,
testyear as Year,
CurrencyAbbr
resident TempTable;
drop table TempTable;
TempTable:
LOAD
YearID as TestYearID,
SubtextRow as Testsubtext,
[Year] as testyear,
if (SubtextRow = 'Kilometers','Kilometer') as DistKilometer
Resident [ResponseTable]
where match(SubtextRow,'Kilometers')>0;
left Join ([ResponseTable])
Load
TestYearID as YearID,
testyear as Year,
DistKilometer
resident TempTable;
drop table TempTable;
Data:
NoConcatenate Load
*,
if(Conversion = 'currency', Response / ApplyMap('conversionMap',AutoNumberHash128(num#(Year),CurrencyAbbr,Conversion),1),
if(Conversion = 'distance' and DistKilometer = 'Kilometer', Response/1.60934,
if(Conversion = 'sqmile' and DistKilometer = 'Kilometer', Response/1.60934*1.60934 ,
if(Conversion = 'acre' and DistKilometer = 'Kilometer', Response/.404686, Response) )))
as ConvertedResponse,
ApplyMap('conversionMap',AutoNumberHash128(num#(Year),CurrencyAbbr,Conversion),1) as USD_Equivalent
resident [ResponseTable];
drop table [ResponseTable];
drop field Response from Data;
rename field ConvertedResponse to Response;
Thanks
