Script question

[jduluc12]

Hi,

My data looks like this.

F1	F2	F3	F4
A	B	2	80
A	C	2	80
A	E-1	4	80
B	D	4	25
B	C	2	25
B	E-2	3	25
C	E-1	2	20
C	E-2	3	20
C	E-3	4	20
D	E-3	5

The desired output is something like this.

E-1	1420
E-2	2205
E-3	5900

The calculation happens something like this.

		Hint	Total
A		F4	80
	B	F4 *F3 (80 *2)	160
	C	F4*F3 ( 80 *2)	160
	E-1	F4*F3 ( 80 *4)	320
B		160 + 25 (F4)	185
	D	185 * 4 (F3)	740
	C	185 * 2 (F3)	370
	E-2	185 * 3 (F3)	555
C		160 + 20 (F4) + 370	550
	E-1	550 * 2 (F3)	1100
	E-2	550 * 3 (F3)	1650
	E-3	550 * 4 (F3)	2200
D			740
	E-3	740 * 5 (F4)	3700

So we achieve this.

E-1	1420
E-2	2205
E-3	5900

I want to write a script to achieve this. 

Is it possible?

Thanks,

[Kushal_Chawda · Accepted solution]

@jduluc12  one solution could be

Data:
Load * Inline [
F1	F2	F3	F4
A	B	2	80
A	C	2	80
A	E-1	4	80
B	D	4	25
B	C	2	25
B	E-2	3	25
C	E-1	2	20
C	E-2	3	20
C	E-3	4	20
D	E-3	5	 ](delimiter is '\t');

New:
Load *,     if(F1<>Previous(F1),F1&'_'&rangesum(keepchar(Peek('Group'),'0123456789'),1),Peek('Group')) as Group
Resident Data
Order by F1,F2;

Drop Table Data;

Final:
Load 0 as Temp
AutoGenerate 0;

for Each vGroup in FieldValueList('Group')

let vF1 = SubField('$(vGroup)','_',1);

let vF1_Number = SubField('$(vGroup)','_',2);

if '$(vF1_Number)' =1 then

Concatenate(Final)
Load *,
     F3*F4 as Required_Value
Resident New
Where F1 = '$(vF1)';

Else

Temp:
Load sum(Required_Value) as F4a
Resident Final
where F2 ='$(vF1)';

let vF4a = Peek('F4a',0,'Temp');

Drop Table Temp;

Concatenate(Final)
Load *,
     F3*rangesum('$(vF4a)',F4)  as Required_Value
Resident New
Where F1 = '$(vF1)';     

EndIf

Next

Drop Fields Temp,Group;

Drop Table New;