Need help to calculate a value dynamically.

[#1]

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

[#2]

Sonal,

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

ApScore:
Mapping LOAD

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:
LOAD Name,
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

[jerem1234]

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!

[maxgro]

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:

LOAD

From+IterNo()-1 as NOP,

Score as NOPScore

resident Table1

While From+IterNo()-1 <= To

;

NOCScore:

LOAD

From+IterNo()-1 as NOC,

Score as NOCScore

resident Table1

While From+IterNo()-1 <= To

;

DROP Table Table1;

Table2:

Load *

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

];

[MarcoWedel · Accepted solution]

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]

Marco,

Many, many good things in this code

Fabrice

[#7]

Thanks a lot Marco...