Not getting correct output from mapping table

[ashis]

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.

[arvind_patil]

Hi Ashish,

Now time to provide sample qvw.

Thanks,

Arvind Patil

[ashis]

Is there any other way if I have negative value in my mapping load table.

because for all other values it is working fine , only problem is getting when has negative value in ID.

Thank you,

[arvind_patil]

try to using join may be it help.

thanks,

Arvind Patil