Multiplying by a percentage in script load

[Anonymous]

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?

[Aurelien_Martinez]

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

[Anonymous]

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.

[Aurelien_Martinez]

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

;

[settu_periasamy]

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]

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!

[settu_periasamy]

No Problem. You are welcome..

[Anonymous]

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.

[settu_periasamy]

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

[Anonymous]

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.