Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Great, please mark the post as solved!
Jordy
Climber
Hi,
The reason for this is probably the two left joins that are done. If the key you use for joining is not unique, you will get duplicates. Please also send the table of the ResponseTable. Maybe it's in the QVF, but I'm on my phone so I can't open the file.
Jordy
Climber
Great, please mark the post as solved!
Jordy
Climber