Help required in LOOKUP

[qlikviewforum]

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

1.3

1.5

2

3

4

5

6

7

8

9

10

12

15

20

25

30

40

50

60

70

80

90

[swuehl]

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?

[qlikviewforum]

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.

[qlikviewforum]

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)

[Ralf-Narfeldt]

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.

[swuehl]

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.

[qlikviewforum]

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.

[swuehl]

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)

[qlikviewforum]

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

[swuehl]

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.