topic Re: Help required in LOOKUP in QlikView

Help required in LOOKUP

qlikviewforum — Sat, 30 May 2015 18:18:25 GMT

In one of the case when I am using lookup to get the required value based on the match found. So when I lookup not sure whether I need to use ROUND,CEIL, etc...Since the values are in different format(as shown below).For your information when I look it up we use to divide the original value and then it has to be formatted to so that it is any of the below so that match is found in mapping table(values in the below mapping table is already divided). Not sure how to manage the different formatting. please help.

VALUE

0.07

0.18

0.25

0.5

0.85

1.3

1.5

Re: Help required in LOOKUP

swuehl — Sat, 30 May 2015 21:07:18 GMT

Not really sure if I understand what you are trying to achieve. Could you upload a small sample QVW?

I assume that the values you are trying to map are a subset of the value set shown above, except for possible calculation errors (errors much smaller than 0.01).

Your mapping values show up to two decimal places, so one could think that it should be enough to round the values you want to map and the mapping set to two decimal places:

=round(Value, 0.01)

But matching floating point values, i.e. compare for equality, it's kind of hard (have a look at HIC's blog post about floating point issues).

So it might be better to multiply all values, the ones you want to map and the mapping value set, with 100 and then round to nearest integer.

=round(Value*100)

Is this of any help for you?

Re: Help required in LOOKUP

qlikviewforum — Sun, 31 May 2015 13:30:34 GMT

Thank you Swuehl! I will try that out and will let you know.

Basically above values which you see is maintained in the excel and basically when we use LOOKUP we need to divide the value by 365 then get corresponding values from the excel(The value which is in the excel is already been divided by 365). Hope it is clear now.

Re: Help required in LOOKUP

qlikviewforum — Mon, 01 Jun 2015 08:18:16 GMT

Hi Swuehl,

To overcome from this problem I am thinking of having below blocks. This I have explained therotically not sure how to build a below block. So basically below block will give only one applymap statement which I want to use in the code. Could you please help me on this as it is little urgent?

If the value of START/365 and END/365 >=1 then I want to use

applymap('Rank_Map',ROUND(START/365,0.1)&'~'&ROUND(END/365,0.1)&'~XYZ',1)

and if the value of START/365 and END/365 <=1 then I want to use

applymap('Rank_Map',FLOOR(START/365,0.1)&'~'&FLOOR(END/365,0.1)&'~XYZ',1)

then there would be some cases where START/365 <=1 and END/365 >=1

applymap('Rank_Map',FLOOR(START/365,0.1)&'~'&ROUND(END/365,0.1)&'~XYZ',1)

and some cases where START/365 >=1 and END/365 <=1

applymap('Rank_Map',ROUND(START/365,0.1)&'~'&FLOOR(END/365,0.1)&'~XYZ',1)

Re: Help required in LOOKUP

Ralf-Narfeldt — Mon, 01 Jun 2015 08:57:10 GMT

If the values in that table already are divided, why not multiply with 365 and you'll always get a value above 1 and can use Round? Or i'm not getting the total picture of which values are in the mapping table and which are in the Excel.

Complete example would help.

Re: Help required in LOOKUP

swuehl — Mon, 01 Jun 2015 09:28:33 GMT

LOAD

if( START/365 >=1 and END/365 >=1,

applymap('Rank_Map',ROUND(START/365,0.1)&'~'&ROUND(END/365,0.1)&'~XYZ',1),

if( START/365 < 1 and END/365 < 1,

applymap('Rank_Map',FLOOR(START/365,0.1)&'~'&FLOOR(END/365,0.1)&'~XYZ',1),

if(START/365 < 1 and END/365 >=1,

applymap('Rank_Map',FLOOR(START/365,0.1)&'~'&ROUND(END/365,0.1)&'~XYZ',1),

applymap('Rank_Map',ROUND(START/365,0.1)&'~'&FLOOR(END/365,0.1)&'~XYZ',1)

)

as MappedValue,

....

But still, mapping a floating point value is problematic. I think better would be to multiply your mapping table (with 3650?) as well as the START and END values (in the mapping functions, with 10) and round them to next integer, to scale all values for mapping to integers.

Re: Help required in LOOKUP

qlikviewforum — Mon, 01 Jun 2015 10:10:57 GMT

Ok I will try it out.

Just to to confirm, I should multiply the mapping table with 3650 and start and end values with 10 and then round both to 0.01. Please correct me if I am wrong.

Re: Help required in LOOKUP

swuehl — Mon, 01 Jun 2015 10:16:20 GMT

Round them to next integer:

Round(Value)

Scaling values should be correct (well, not 100% sure, as Ralf said, it's better to see the real values and validate the approach).

Could you come up with a small sample QVW?

edit: I am also unsure about the need to use floor() function, but if you need it, also approach nearest integer:

floor(Value)

Re: Help required in LOOKUP

qlikviewforum — Mon, 01 Jun 2015 10:23:21 GMT

Thanks Swuehl and Ralf. I will check both the stuffs and will let you know.

Re: Help required in LOOKUP

swuehl — Mon, 01 Jun 2015 10:27:05 GMT

I just noticed that you are actually not mapping floating point values, but text values (since you concatenate the values rounded to one decimal places). There should be no (or less 😉 ) issues mapping text values, so you don't need to scale the values.

Re: Help required in LOOKUP

qlikviewforum — Mon, 01 Jun 2015 10:32:16 GMT

I am wondering whether I can convert the mapping field values in the excel to text and then map it? Just a thought!

Re: Help required in LOOKUP

qlikviewforum — Mon, 01 Jun 2015 11:22:22 GMT

Sorry I didn't get this

Re: Help required in LOOKUP

Ralf-Narfeldt — Mon, 01 Jun 2015 11:32:14 GMT

I think it would be good to see all your indata, to get the full picture. you have one Excel file (with START and END), and then the mapping table in your first post, right?

Re: Help required in LOOKUP

qlikviewforum — Mon, 01 Jun 2015 14:59:16 GMT

This block didn't work. It is giving null for few of the records. Not sure what is going wrong here. Also wherever 1.0 is expected it is giving 1.00. Please help

Re: Help required in LOOKUP

Ralf-Narfeldt — Tue, 02 Jun 2015 05:21:51 GMT

Please post some examples of all your data tables.

Re: Help required in LOOKUP

qlikviewforum — Wed, 03 Jun 2015 06:22:01 GMT

Sorry for late response. Due to security reasons I will not be able to attach the sample data.

Thank you so much for your kind help. I think above block is working fine after removing the FLOOR but need to do through testing once. Will keep you updated on the same.