topic Multiplying by a percentage in script load in QlikView

Multiplying by a percentage in script load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

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

TABLE A:

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

TABLE B:

REGION	PERCENTAGE_1	PERCENTAGE_2
A	50%	50%
B	20%	50%
C	75%	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/N	REGION	FLAG	MODEL
8	A	X	FFF
10	C		XXX

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?

Re: Multiplying by a percentage in script load

Aurelien_Martinez — Tue, 07 Jun 2016 12:42:38 GMT

Hi,

Pourcentage1:

MAPPING LOAD

REGION,

PERCENTAGE_1

FROM MyTAblePer

;

Pourcentage2:

MAPPING LOAD

REGION,

PERCENTAGE_2

FROM MyTAblePer

;

Load

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

REGION,

FLAG,

MODEL

FROM MYTAble

;

Aurélien

Re: Multiplying by a percentage in script load

Anonymous — Tue, 07 Jun 2016 13:46:25 GMT

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.

Re: Multiplying by a percentage in script load

Aurelien_Martinez — Tue, 07 Jun 2016 13:53:47 GMT

Pourcentage1:

MAPPING LOAD

REGION,

PERCENTAGE_1

FROM MyTAblePer

;

Pourcentage2:

MAPPING LOAD

REGION,

PERCENTAGE_2

FROM MyTAblePer

;

Load

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

;

Re: Multiplying by a percentage in script load

settu_periasamy — Tue, 07 Jun 2016 13:58:09 GMT

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;

Re: Multiplying by a percentage in script load

Anonymous — Tue, 07 Jun 2016 15:03:20 GMT

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!

Re: Multiplying by a percentage in script load

settu_periasamy — Tue, 07 Jun 2016 15:20:45 GMT

No Problem. You are welcome..

Re: Multiplying by a percentage in script load

Anonymous — Tue, 07 Jun 2016 17:10:53 GMT

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?

Thanks in advance.

Re: Multiplying by a percentage in script load

settu_periasamy — Tue, 07 Jun 2016 17:25:56 GMT

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

Re: Multiplying by a percentage in script load

Anonymous — Tue, 07 Jun 2016 17:57:23 GMT

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.

Re: Multiplying by a percentage in script load

Anonymous — Tue, 07 Jun 2016 17:58:03 GMT

This is the Result I am trying to achieve.

Re: Multiplying by a percentage in script load

Anonymous — Wed, 08 Jun 2016 13:31:18 GMT

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)

LOAD REGION,

FLAG,

Sum(PERCENTAGE_1) as Val1,

Sum(PERCENTAGE_FLAG) as ValFLAG

Resident T1 Group by REGION,FLAG;

NoConcatenate

Temp_TABLE:

LOAD [S/N],

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:

first '$(vTEMP)' LOAD

* ,

0 as test

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

ENDIF

DROP Table Temp_TABLE;

Thanks!