# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Not applicable

## Need help to calculate a value dynamically.

I am new to Qlikview, need help to calculate a value dynamically.

I have 2 tables A, B respectively.

 From To Score 1 10 1 11 20 2 21 30 3 31 40 4 41 50 5 111 120 6 541 550 7

 Name NOP NOC R1 15 28 R2 29 42 R3 33 48 R4 23 13 R5 117 542 R6 46 37 R7 545 116

How to calculate the score if the value in between From and To.

Ex.

NOP is 15 it should have score 2

NOC is 28 is should have score 3

In the same way need a table like

 Name NOP NOC NOP Score NOC Score R1 15 28 2 3 R2 29 42 3 5 R3 33 48 4 5 R4 23 13 3 2 R5 117 542 6 7 R6 46 37 4 4 R7 545 116 7 6
1 Solution

Accepted Solutions
Highlighted
MVP

Hi Sonal,

just one more solution, this time using the intervalmatch function, which I find to be very helpful in cases of ... well ... intervalmatching 😉

regards

Marco

result:

used code:

tabA:

LOAD From, To, Score FROM [http://community.qlik.com/message/481038]

(html, codepage is 1252, embedded labels, table is @1);

tabB:

LOAD Name, NOP, NOC FROM [http://community.qlik.com/message/481038]

(html, codepage is 1252, embedded labels, table is @2);

Left Join IntervalMatch(NOP) LOAD From, To Resident tabA;

Left Join LOAD From, To, Score as NOPScore Resident tabA;

DROP Fields From, To From tabB;

Left Join IntervalMatch(NOC) LOAD From, To Resident tabA;

Left Join LOAD From, To, Score as NOCScore Resident tabA;

DROP Fields From, To From tabB;

thanks to swuehl for the nice Re: Merge Continuous Intervals of loading example tables directly from the thread itself.

6 Replies
Highlighted
Not applicable

Sonal,

First I will create a MAPPING table with all the possible lines, sth like:

ApScore:

From+IterNo()-1 as Points,
Score
Resident Score

While From+IterNo()-1 <= To ;

WHERE

Score is your first table containing the different intervals

IterNo() a looping function starting at 1 (hence From+IterNo()-1)

Secondly, I use with the ApplyMap() function:

Data:
ApplyMap('ApScore', NOP, 7) as NOPScore,
ApplyMap('ApScore', NOC, 7) as NOCScore,
NOP,
NOC

FROM xxxxx;

WHERE

ApplyMap is a function that will use the mapping table (2 fields only). The 3d argument is a default, in caseit does not find because the data is too high for example

The mapping table is a temp table deleted by QV at the end of the script.

Fabrice

Highlighted
Specialist II

See attached for example. Since your intervals a regular and each start at the beginning of tens, you could just use From and Score.

You can create two extra fields like:

floor(NOP-1, 10)+1 as NOPmin

floor(NOC-1, 10)+1 as NOCmin

Then use an sum(if()) like:

sum(if(NOPmin=From, Score))

The thing is, sum(if()) might take a toll on performance if you are using a large data set. If that's the case, you should move all into script the way AUNEZ FABRICE does.

If your intervals are regular and sets of 10, you may get away with just use a mapping of From, Score. Then with something like:

Applymap('map1', floor(NOP-1, 10)+1) as NOPScore

But this is dependent on your data.

Hope this helps!

Highlighted
MVP

I prefere to explode the From-To-Score table in 2 tables; the Name-NOP_NOC table will automatically binds (associates) to the scores

Thanks to Fabrice for IterNo()

Table1:

Load From, To, Score Inline [

From,To,Score

1,10,1

11,20,2

21,30,3

31,40,4

41,50,5

111,120,6

541,550,7

];

NOPScore:

From+IterNo()-1 as NOP,

Score as NOPScore

resident Table1

While From+IterNo()-1 <= To

;

NOCScore:

From+IterNo()-1 as NOC,

Score as NOCScore

resident Table1

While From+IterNo()-1 <= To

;

DROP Table Table1;

Table2:

Inline [

Name,NOP,NOC

R1,15,28

R2,29,42

R3,33,48

R4,23,13

R5,117,542

R6,46,37

R7,545,116

];

Highlighted
MVP

Hi Sonal,

just one more solution, this time using the intervalmatch function, which I find to be very helpful in cases of ... well ... intervalmatching 😉

regards

Marco

result:

used code:

tabA:

LOAD From, To, Score FROM [http://community.qlik.com/message/481038]

(html, codepage is 1252, embedded labels, table is @1);

tabB:

LOAD Name, NOP, NOC FROM [http://community.qlik.com/message/481038]

(html, codepage is 1252, embedded labels, table is @2);

Left Join IntervalMatch(NOP) LOAD From, To Resident tabA;

Left Join LOAD From, To, Score as NOPScore Resident tabA;

DROP Fields From, To From tabB;

Left Join IntervalMatch(NOC) LOAD From, To Resident tabA;

Left Join LOAD From, To, Score as NOCScore Resident tabA;

DROP Fields From, To From tabB;

thanks to swuehl for the nice Re: Merge Continuous Intervals of loading example tables directly from the thread itself.

Highlighted
Not applicable

Marco,

Many, many good things in this code

Fabrice

Highlighted
Not applicable

Thanks a lot Marco...