Valuing dimensions with members # using already available rows

[andreag412]

Dear experts,
I would need some help with the development of a Data Action.
After a first step of Cross-Model Copy the row situation is as follows:

View	Entity	Market	Products	Customer	Channel	Sales Category	Intercompany	Distr. Flow	Net Sales (Local)	Std Cost EoL (Local)	Std Cost Mark Up Reverse (Local)
#	V10	VN	Prod1	DEALER	RET	COMMON	#	STOCK	10000	–	–
#	V10	#	Prod1	#	#	#	#	#	–	500	0

I have taken 2 rows as an example, the purpose would be to go and “merge” the two rows. So with end result the following:

View	Entity	Market	Products	Customer	Channel	Sales Category	Intercompany	Distr. Flow	Net Sales (Local)	Std Cost EoL (Local)	Std Cost Mark Up Reverse (Local)
#	V10	VN	Prod1	DEALER	RET	COMMON	#	STOCK	10000	500	0

Do you have any advice on how this can be done?

Thanks for the help

[MichalCK · Accepted solution]

hi @andreag412 ,

to achieve your goal, you would need to. Aggregate data into a variables - the dimensions you want to merge (like Customer, Channel, Sales...), Next you put IF statement to process only rows with Net Sales and overwrite measure values with the aggregated ones. Something like that

VARIABLEMEMBER #Customer OF [d/Customer]
VARIABLEMEMBER #Channel  OF [d/Channel]
VARIABLEMEMBER #Scat 	 OF [d/SalesCat]
VARIABLEMEMBER #IC       OF [d/Intercompany]

DATA([d/Cusomter] = #Customer,
	 [d/Channel] = #Channel,
	 [d/Scat] = #Scat,
	 [d/Intercompany] = #IC) = RESULTLOOKUP()

IF RESULTLOOKUP([d/Measures]="NetSales") != NULL THEN
	DATA([d/Measures] = "StdCost") = RESULTLOOKUP([d/Measure] = "StdCost"
												  [d/Cusomter] = #Customer,
												  [d/Channel] = #Channel,
												  [d/Scat] = #Scat,
												  [d/Intercompany] = #IC)
	DATA([d/Measures] = "StdCostMark") = RESULTLOOKUP([d/Measure] = "StdCostMark"
												  [d/Cusomter] = #Customer,
												  [d/Channel] = #Channel,
												  [d/Scat] = #Scat,
												  [d/Intercompany] = #IC)	
ENDIF

 I'm not sure if this code will work, just drafted it. But it can be achieved this way.

[MichalCK · Accepted solution]

one thing, this is not deleting the exisitng row with #, but that I would leave for another discussion

[andreag412 · Accepted solution]

Thanks for the help, I have implemented this logic: But we don't get the desired result, because the table shows:

[andreag412 · Accepted solution]

View Entity Market Products Customer Channel Sales Category Intercompany Distr. Flow Net Sales (Local) Std Cost EoL (Local) Std Cost Mark Up Reverse (Local) # V10 VN Prod1 DEALER RET COMMON # STOCK 10000 – – # V10 # Prod1 # # # # # 500 500 500