topic Re: Not getting correct output from mapping table in QlikView

Not getting correct output from mapping table

ashis — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Not getting correct output from mapping table

techvarun — Wed, 02 Aug 2017 09:46:13 GMT

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

Re: Not getting correct output from mapping table

Chanty4u — Wed, 02 Aug 2017 09:46:16 GMT

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

Re: Not getting correct output from mapping table

ashis — Wed, 02 Aug 2017 10:05:02 GMT

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.

Re: Not getting correct output from mapping table

ashis — Wed, 02 Aug 2017 10:13:06 GMT

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

Re: Not getting correct output from mapping table

Chanty4u — Wed, 02 Aug 2017 10:14:30 GMT

try simple expression

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

Re: Not getting correct output from mapping table

ashis — Wed, 02 Aug 2017 10:20:26 GMT

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

Re: Not getting correct output from mapping table

ashis — Thu, 03 Aug 2017 06:45:04 GMT

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.

Re: Not getting correct output from mapping table

arvind_patil — Thu, 03 Aug 2017 06:48:36 GMT

Hi Ashis,

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

Thanks,

Arvind Patil

Re: Not getting correct output from mapping table

ashis — Thu, 03 Aug 2017 07:14:53 GMT

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;

Re: Not getting correct output from mapping table

arvind_patil — Thu, 03 Aug 2017 07:39:08 GMT

Hi Ashish,

Now time to provide sample qvw.

Thanks,

Arvind Patil

Re: Not getting correct output from mapping table

ashis — Thu, 03 Aug 2017 07:59:50 GMT

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,

Re: Not getting correct output from mapping table

arvind_patil — Thu, 03 Aug 2017 08:02:12 GMT

try to using join may be it help.

thanks,

Arvind Patil