# 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

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

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?