Script question

jduluc12 — Thu, 08 Jun 2023 07:50:02 GMT

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	F4F3 ( 80 2)	160
	E-1	F4F3 ( 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,

Re: Script question

Kushal_Chawda — Wed, 14 Jun 2023 10:44:16 GMT

@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;

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

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

topic Script question in App Development

Script question

Re: Script question

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