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.

[techvarun]

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

[Chanty4u]

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
];

[ashis]

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.

[ashis]

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

[Chanty4u]

try simple expression

1. LOAD *, 
2.      Interval(JOB_ONSITETIME-JOB_CREATETIME, 'h.mm') as JOB_RESPTIME 
3. INLINE [ 
4.     JOB_CREATETIME, JOB_ONSITETIME 
5.     12/29/2016 00:12:16, 12/29/2016 00:12:20 
6. ]; 

[ashis]

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
];

[ashis]

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.

[arvind_patil]

Hi Ashis,

Kindly check -4:00 avalibale in both the places. In Mapping Master and Base data.

Thanks,

Arvind Patil

[ashis]

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;