Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello ,
In my source table i have two date fields name ReportUserTime and ReportTime, and its format as as follow
7/11/2017 11:31:01 AM
now based on these two columns i am calculating GMT (Difference between two time) using
interval(time(StartTimeReportUserTimeZone, 'hh:mm') - time(StartTimeReport, 'hh:mm'), 'h:mm') as GMTID
and saving in one qvd. The output of the GMTID are 1:00,2:00,5:30 etc
Now based on these GMTID i need to show location , for that i am creating a mapping table as below.
map1:
Mapping load
GMTID,Location
INLINE [
GMTID,Location
1:00,London
2:00,Europe
5:30,India
8:00,Hong Kong
];
LOAD *,
applymap('map1',GMTID,'Location not Found') as Location
Resident fromqvd;
somehow this is not working i am getting Location output as Location not found for all the rows.
Please help.
Works Perfect for me
map1:
Mapping load
GMTID,Location
INLINE [
GMTID,Location
1:00,London
2:00,Europe
5:30,India
8:00,Hong Kong
];
Master:
LOAD * INLINE [
GMTID
1:00
2:00
5:30
8:00
];
LOAD *,
applymap('map1',GMTID,'Location not Found') as Location
Resident Master;
DROP TABLE Master;
Please check the data in your QVD
i dont see any issue.
is GMTID getting the value? and are you taking correct resident table name?
if yes try to rename location and try ,
map1:
Mapping load
GMTID,
Location as New_Location
INLINE [
GMTID,Location
1:00,London
2:00,Europe
5:30,India
8:00,Hong Kong
];
LOAD *,
applymap('map1',GMTID,'Location not Found') as Location
Resident fromqvd;
just for ref small example below
//Mapping Table
CustomerTable:
Mapping Load * Inline
[
CustomerID, Country
A, Germany
B, France
C, Italy
D, Poland
E, Spain
Z, UK
];
Sales:
Load *,
ApplyMap('CustomerTable', CustomerID, 'Unknown') as CustomerCountry
Inline
[
CustomerID, Month, Sales
A, Jan, 100
B, Jan, 200
C, Jan, 300
D, Feb, 150
E, Mar, 200
F, Apr, 100
A, May, 140
D, May, 80
E, May, 100
F, Jun, 150
];
I am getting GMTID there is not issue. as I said I am getting GMT id as , 1:00, 5:30, 2:00 eg.
I am suspecting that the following code that calculating the GMT is the culprit.
time(interval(time(StartTimeReportUserTimeZone, 'hh:mm') - time(StartTimeReport, 'hh:mm'), 'h:mm'),'h:mm') as GMTID
apart from that i do not see any issue in my code.
may be the above code is coming as text.
I am able to resolve the issue by putting the following syntax
time#(interval(time(StartTimeReportUserTimeZone, 'hh:mm') - time(StartTimeReport, 'hh:mm'), 'h:mm'),'h:mm') as GMTID
try simple expression
Not it is not working for Canada, any idea why.
map1:
Mapping load
GMTID,Location
INLINE [
GMTID,Location
1:00,London
2:00,Europe
5:30,India
8:00,Hong Kong
-4:00,Canada
];
Can anyone help me , why -4:00 , Canada is not working.
map1:
Mapping load
GMTID,Location
INLINE [
GMTID,Location
1:00,London
2:00,Europe
5:30,India
8:00,Hong Kong
-4:00,Canada
];
LOAD *,
applymap('map1',GMTID,'Location not Found') as Location
Resident QVD;
For Canada , I am getting Location not Found.
Please help.
Hi Ashis,
Kindly check -4:00 avalibale in both the places. In Mapping Master and Base data.
Thanks,
Arvind Patil
yes in base table it is there and I have also mention in the mapping table.
map1:
Mapping load
GMTID,Location
INLINE [
GMTID,Location
1:00,London
2:00,Europe
5:30,India
8:00,Hong Kong
-4:00,Canada
];
LOAD *,
applymap('map1',GMTID,'Location not Found') as Location
Resident qvd;