Table vlookup equivalent

[Anonymous]

Hi guys

Need help here.

I load a table and need to do something equivalent of a vlookup in excel.  For a given number of sales in a month I want an incentive amount to pull through. My data structure is complex, so i have to do a set analysis in my counts.  (incorporated this in my example)

Any ideas, attached is a example of the model and excel data (in excel the vlookup, how i would expect it to work, is on the last sheet).

Thanks in advance.

Cheers

[hic]

You could use applymap to map a sales value to an incentive and then create a field Incentive already at the script run. See more on http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap.

If you have many different sales amounts, you may want to work ranges instead. Then you should look at IntervalMatch to create the mapping table.

HIC

[Anonymous]

Unfortunately I need this to work on the front end in the table. If I'm not mistaken maps are only in the script?  Is there a way to enable maps to be used in the front-end?

[Anonymous]

The best I can come up with is fieldvalue. 

Now FieldValue has its flaws because it goes on load order rather than order by sort (irritating). 

That means I can't load a "watered-down" table, write a loop to fill in the in-between numbers and use fieldvalue, I have to have the raw table with all the numbers from the beginning.

Am I making sense here and over-complicating this?

[Anonymous]

Ok I cant get the fieldValue to work.  I load inline then use the fieldvalue function on front end:

"IncentiveTable":

load * inline [

Sales,          Incentive

0,          0

1,          0

2,          50

3,          50

4,          80

5,          80

6,          110

7,          110

8,          140

9,          140

10,          170

];

FieldValue('Incentive', 4) gives me 110 when I am expecting 50!!!

help please...

[Vegar]

You are asking for the fourth value in the Incentive field. the fourt value is 110

1 = 0

2 = 50

3 = 80

4 = 110

5= 140

6=170

If you want to reach the 50-value try set-analysis, e.g. only({<Sales={4}>}Incentive) .

Best regards

Vegar Lie Arntsen

QlikView consultant at egbs consulting ab

Blog (in Swedish): bi-effekten.se