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?

[Anonymous]

This is the Result I am trying to achieve.

[Anonymous · Accepted solution]

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!