Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## 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
Anonymous
Not applicable
Author

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
Partner - Specialist II

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

Help users find answers! Don't forget to mark a solution that worked for you!
Anonymous
Not applicable
Author

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 - Specialist II

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

;

Help users find answers! Don't forget to mark a solution that worked for you!
Master III

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;
Anonymous
Not applicable
Author

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!

Master III

No Problem. You are welcome..

Anonymous
Not applicable
Author

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?