Qlik Community

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 
Search instead 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

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:

           QlikCommunity_Thread_109434.JPG.jpg

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.

View solution in original post

6 Replies
Highlighted
Not applicable

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

Highlighted
Specialist II
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
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()

Clipboard01.jpg

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

];

Highlighted

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:

           QlikCommunity_Thread_109434.JPG.jpg

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.

View solution in original post

Highlighted
Not applicable

Marco,

Many, many good things in this code

Fabrice

Highlighted
Not applicable

Thanks a lot Marco...