Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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!
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
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.
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
;
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;
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!
No Problem. You are welcome..
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.
But you need one value any of the 3, right (for e.g Region 'A' having 3 value with 100%)
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.