Foreach confusion in SAC Advanced Formula

[Malya]

Hi Experts

need help with understanding Foreach in SAC Advanced formula. i was getting different output so for understanding I wrote simple foreach loop. still it surprises me with the result.

==========

MEMBERSET [d/Date] = ("202501","202502","202503")

MEMBERSET [d/Y_FUNCAREA] = ("4100","5110","7130","4250","7280")

VARIABLEMEMBER #X OF [d/Y_FUNCAREA]

FOREACH.BOOKED [d/Y_FUNCAREA]

DATA([d/Y_FUNCAREA] = #X) = RESULTLOOKUP()

ENDFOR 

DATA([d/Y_FUNCAREA]="9307") = RESULTLOOKUP([d/Y_FUNCAREA] = #X)

=============

expecting this will copy the data from  the last functional area in the loop to the target  Functional area ie 9307. 

to understand the output created story with PC and FA, the data looks like for some PC it added two FAs and for some it only considered one FA. could anyone explain why it is adding two FA for some please.

Thanks very much in Advance

[N1kh1l · Accepted solution]

@Malya 

I do not see any reason for them to get added. With foreach on FA it will keep replacing the FA one by one. Try debugging the code and see how the loop progresses. In my simple test it did not add anything

This is how the loop progressed in debug

FA
4100	1	202401	PC1	#X	45.9
	2	202401	PC2	#X	26.78
4250	1	202401	PC1	#X	4.59
	2	202401	PC2	#X	26.78
	3	202401	PC4	#X	5.74
5110	1	202401	PC1	#X	4.59
	2	202401	PC2	#X	41.06
	3	202401	PC4	#X	5.74
7130	1	202401	PC1	#X	2.04
	2	202401	PC2	#X	41.06
	3	202401	PC4	#X	2.55
7280	1	202401	PC1	#X	2.04
	2	202401	PC2	#X	35.7
	3	202401	PC4	#X	2.55
	4	202401	PC3	#X	17

Hope this helps. 

Try clearing the data and debug with few records. The only reason to aggregate will be if after the loop you get identical records with all key matching.

Nikhil

[Malya · Accepted solution]

Thanks Nikhil for your reply

[Malya · Accepted solution]

I will clean up data and will try again, but i observed your screenshot it takes different FA for different PCs for PC1-> it took value from 7130 and pc2 ->7280 and so on, could you please clarify why it is taken form different FAs, if we are looping on FA it should always group by FA and take the last one isn't it. Thanks Malya

[N1kh1l · Accepted solution]

Malya, Just look at the debug table. Each FA enters the loop 1 at time. Since you are replacing the FA by #X if the next FA had records with same PC combination as previous it will be replaced. So by the end of the loop only 4 records remain which will get assigned to 9370