Data Distribution

[mahendragaur]

Hello Amigo,

I have data of different people based on weeks (refer below screenshot)-

Name	W1	W2	W3	W4	W5
A	0	0	50	30	0
B	0	5	60	20	20
C	0	10	100	10	10
D	0	50	50	50	50
E	0	20	10	60	11
F	0	10	60	20	5

and I want to distribute it in 5 category for all weeks-

and distribution should be based on below table

Category	multiplication factor
1	0.5
2	0.2
3	0.1
4	0.2
5	0

expected output-

Name	Category	W1	W2	W3	W4	W5
A	1	0	0	25	15	0
A	2	0	0	10	6	0
A	3	0	0	5	3	0
A	4	0	0	10	6	0
A	5	0	0	0	0	0
B	1	0	2.5	30	10	10
B	2	0	1	12	4	4
B	3	0	0.5	6	2	2
B	4	0	1	12	4	4
B	5	0	0	0	0	0
C	1	0	5	50	5	5
C	2	0	2	20	2	2
C	3	0	1	10	1	1
C	4	0	2	20	2	2
C	5	0	0	0	0	0

that means 1st category should be multiplied by 0.5 and 2nd by 0.2 and so on...

[el_aprendiz111]

Hi,

2 options

1 SCRIPT

2 CHART

TMP:
LOAD * Inline
[
Name,W1,W2,W3,W4,W5
A,0,0,50,30,0
B,0,5,60,20,20
C,0,10,100,10,10
D,0,50,50,50,50
E,0,20,10,60,11
F,0,10,60,20,5
]
;



LET vNamFil = noofrows('Name');

FOR i = 1 TO NoOfRows('TMP') 

LET vTbl = FieldValue('Name', $(i)); 

FOR x = 1 to 5;
UNQUALIFY*;
DATA: 
LOAD *, $(x) AS Category
Resident TMP
Where Name = '$(vTbl)'


;

NEXT   

NEXT 


DROP TABLE TMP; 

Inner Join

Factor:

LOAD
num(Category) as Category,
Replace([multiplication factor],'.',',') As [multiplication factor]
Inline
[
Category,multiplication factor
1,0.5
2,0.2
3,0.1
4,0.2
5,0
];


SUMARY:
LOAD
Name AS _Name,
W1*[multiplication factor] AS _W1,
W2*[multiplication factor] AS _W2,
W3*[multiplication factor] AS _W3,
W4*[multiplication factor] AS _W4,
W5*[multiplication factor] AS _W5
Resident DATA
Order By Name asc
;