New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Highlighted
Contributor II

Multiplying by a percentage in script load

Hi all,

I have a table which I would like to multiply by another as seen below:

TABLE A:

S/NREGIONFLAGMODEL
1AXYYY
2AXXX
3BXYYY
4BXFFF
6C

YYY

7CXFFF
8AXFFF
9AXYYY
10CXXX

TABLE B:

REGIONPERCENTAGE_1PERCENTAGE_2
A50%50%
B20%50%
C75%20%

So, what I would like to do in script is to multiply all the S/N from a given region by a PERCENTAGE_1 (for non S/N without X in flag) and by PERCENTAGE 2 for those which has X in flag. The result can be an aleatory S/N, but cannot be duplicated. rounded to an integer number.

The output would be as below:

TABLE_RESULT:

S/NREGIONFLAGMODEL
8AXFFF
10CXXX

To understand better the table, all the S/N was grouped by region. After that, those which hadn't the flag was multiplied by PERCENTAGE_1 and those which had was multiplied by PERCENTAGE_2.

EX: all S/N from REGION A = 4

With flags = 3  * Percentage_2 = 1,5   (round down to 1)  the result should be any of the 3 S/N
Without flags = 1 * Percentage_1 = 0,5   (round down) the result = 0

Does anyone have an idea to do that?

1 Solution

Accepted Solutions
Contributor II

Re: Multiplying by a percentage in script load

Guys,

I just implemented the result given by settu.periyasamy‌ in order to get the desirable result.

T1:

LOAD * INLINE [

S/N, REGION, FLAG, MODEL

1, A, X, YYY

2, A,  , XXX

3, B, X, YYY

4, B, X, FFF

6, C,  , YYY

7, C, X, FFF

8, A, X, FFF

9, A, X, YYY

10, C, , XXX

];

Left Join(T1)

LOAD * INLINE [

REGION, PERCENTAGE_1, PERCENTAGE_FLAG

A, 100%, 40%

B, 100%, 50%

C, 100%, 100%

];

Left Join(T1)

FLAG,

Sum(PERCENTAGE_1) as Val1,

Sum(PERCENTAGE_FLAG) as ValFLAG

Resident T1 Group by REGION,FLAG;

NoConcatenate

Temp_TABLE:

REGION,

FLAG,

MODEL,

If(FLAG='X',floor(ValFLAG),floor(Val1)) as Temp_val Resident T1;

DROP Table T1;

for v = 0 to NoOfRows('Temp_TABLE')-1;

let vREGION = peek('REGION',v, 'Temp_TABLE');

let vFLAG   = peek('FLAG',v, 'Temp_TABLE');

let vTEMP   = peek('Temp_val',v, 'Temp_TABLE');

if('\$(vTtemp)' <>0) then

for t = 1 to \$(vTEMP)

TABLER:

* ,

0 as test

resident Temp_TABLE where REGION = '\$(vREGION)' and FLAG = '\$(vFLAG)' and Temp_val = '\$(vTEMP)';

NEXT

ENDIF

NEXT

DROP Table Temp_TABLE;

Thanks!

11 Replies Partner

Re: Multiplying by a percentage in script load

Hi,

Pourcentage1:

REGION,

PERCENTAGE_1

FROM MyTAblePer

;

Pourcentage2:

REGION,

PERCENTAGE_2

FROM MyTAblePer

;

If(FLAG = 'X', [S/N] * ApplyMap('Pourcentage1', REGION), [S/N] * ApplyMap('Pourcentage2', REGION)) as [New S/N],

REGION,

FLAG,

MODEL

FROM MYTAble

;

Aurélien

Contributor II

Re: Multiplying by a percentage in script load

Thanks for replying, but in this case the script will pass through each S/N and apply the percentage, which can give an incorrect result.

What I would like to do is group all the S/N by region and then apply the percentage, as I explained in the example. Partner

Re: Multiplying by a percentage in script load

Pourcentage1:

REGION,

PERCENTAGE_1

FROM MyTAblePer

;

Pourcentage2:

REGION,

PERCENTAGE_2

FROM MyTAblePer

;

If(FLAG = 'X', Sum([S/N]) * ApplyMap('Pourcentage1', REGION), Sum([S/N]) * ApplyMap('Pourcentage2', REGION)) as [New S/N],

REGION,

FLAG

FROM MYTAble

group by REGION, FLAG

; MVP

Re: Multiplying by a percentage in script load

Hi,

How about the 'Region' B? I'm getting the data for Region 'B' also using the below script:

 T1: LOAD * INLINE [ S/N, REGION, FLAG, MODEL 1, A, X, YYY 2, A, , XXX 3, B, X, YYY 4, B, X, FFF 6, C, , YYY 7, C, X, FFF 8, A, X, FFF 9, A, X, YYY 10, C, , XXX ]; Left Join(T1) LOAD * INLINE [ REGION, PERCENTAGE_1, PERCENTAGE_2 A, 50%, 50% B, 20%, 50% C, 75%, 20% ]; Left Join(T1) LOAD  REGION, FLAG, Sum(PERCENTAGE_1) as Val1, Sum(PERCENTAGE_2) as Val2 Resident T1 Group by REGION,FLAG; NoConcatenate Temp: LOAD  [S/N], REGION, FLAG, MODEL, If(FLAG='X',floor(Val2),floor(Val1)) as Temp_val Resident T1; DROP Table T1; NoConcatenate Final: LOAD Max([S/N]) as [S/N], REGION, FLAG, MaxString(MODEL) as MODEL Resident Temp Where Temp_val=1 Group by REGION,FLAG ; DROP Table Temp;
Contributor II

Re: Multiplying by a percentage in script load

hI settu.periyasamy.

I haven't done for REGION B, my bad. Your result is correct.

Just checked the script and everything is working fine.

Thank you very much! MVP

Re: Multiplying by a percentage in script load

No Problem. You are welcome..

Contributor II

Re: Multiplying by a percentage in script load

hi settu.periyasamy‌.

In your example, please replace the percentage_2 for region A from 50% to 100%. It should retrieve all the Flagged S/N for this region. I tried to change the line below but with no results unfortunately.

Resident Temp Where Temp_val=1 Group by REGION,FLAG ;

Resident Temp Where Temp_val>=1 Group by REGION,FLAG;

It only retrieves one result per Flag/REGION. So how can I retrieve more than one? MVP

Re: Multiplying by a percentage in script load

But you need one value any of the 3, right (for e.g Region 'A' having 3 value with 100%)

Contributor II

Re: Multiplying by a percentage in script load

I need the result of the percentage calculation. Taking Percentage_2 as 75% for REGION A and multiplying by total flagged number (3), the result should be 2,25 (2 rounded). So in this case we have 2 different S/N flagged in region A.