# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for
Did you mean:
Highlighted Creator 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
Highlighted Creator II

Guys,

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

T1:

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)

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
Highlighted Partner

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

Highlighted Creator II

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.

Highlighted Partner

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

;

Highlighted MVP

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;
Highlighted Creator II

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!

Highlighted MVP

No Problem. You are welcome..

Highlighted Creator II

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?

Highlighted MVP

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

Highlighted Creator II

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.  