Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
anselmo_meira
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
anselmo_meira
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)

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)';

  NEXT

  ENDIF

  NEXT

DROP Table Temp_TABLE;

Thanks!

11 Replies
Partner
Partner

Re: Multiplying by a percentage in script load

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

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

Re: Multiplying by a percentage in script load

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

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;

anselmo_meira
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!

Re: Multiplying by a percentage in script load

No Problem. You are welcome..

anselmo_meira
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?

Thanks in advance.


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%)

anselmo_meira
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.