Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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
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!
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
];
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.
Marco,
Many, many good things in this code
Fabrice
Thanks a lot Marco...