topic Re: Table vlookup equivalent in QlikView

Table vlookup equivalent

Anonymous — Tue, 18 Sep 2012 16:15:58 GMT

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

Re: Table vlookup equivalent

hic — Tue, 18 Sep 2012 19:23:25 GMT

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

Re: Table vlookup equivalent

Anonymous — Tue, 18 Sep 2012 21:02:38 GMT

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?

Re: Table vlookup equivalent

Anonymous — Tue, 18 Sep 2012 21:21:20 GMT

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?

Re: Table vlookup equivalent

Anonymous — Tue, 18 Sep 2012 21:43:15 GMT

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...

Re: Table vlookup equivalent

Vegar — Tue, 25 Jun 2013 09:07:26 GMT

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