Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
Creator II
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
Creator II

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!

View solution in original post

11 Replies
Highlighted
Partner
Partner

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

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

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

;

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

Thanks in advance.


Highlighted

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

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