GROUP BY IS NOT WORKING

[SCM]

BNM_SURVEY:
LOAD
Account,
Curr.,
SubField(ApplyMap('VENDOR_MASTER_DATA',Account,Null()),'|',1) as [VENDOR NAME],
SubField(ApplyMap('VENDOR_MASTER_DATA',Account,Null()),'|',2) as [CTY],
"Sum of Amount in doc. curr." as [FAMTCR],
"Sum of Amount in local cur." as [LAMTCR],
"Sum of Amount in doc. curr.1" as [FAMTDB],
"Sum of Amount in local cur.1" as [LAMTDB]
FROM [lib://BNM SURVEY/FBL1N-0119-0319.xlsx]
(ooxml, embedded labels, header is 2 lines, table is Sheet2);

Left Join

LOAD
Account,
'VENDOR NAME',
CTY,
Curr.,
"Sum of FAMTBF" as [FAMTBF],
"Sum of LAMTBF" as [LAMTBF]
FROM [lib://BNM SURVEY/BF.xlsx]
(ooxml, embedded labels, table is FBL1N);

LOAD
Sum([FAMTBF])+ Sum([FAMTDB])+ Sum([FAMTCR])as [FAMTCF],
Sum([LAMTBF])+ Sum([LAMTDB])+ Sum([LAMTCR])as [LAMTCF]
Resident BNM_SURVEY

Group By
Account,
[Curr.],
[CTY];

THE RESULT OF THE SUM ON THE RESIDENT IS NOT ACHIEVE ALL THE LINE ITEM AMOUNT ARE SAME ON COLUMN SUM(FAMTCF).

CAN YOU LET ME KNOW WHERE IS THE MISTAKE.

[jonathandienst · Accepted solution]

You need to include the dimension fields in the last load. Without them, FAMTCF and LAMTCF are only global totals not associated with the dimensions in any way. Change the last load statement:

LOAD
	Account,
	[Curr.],
	[CTY],
	Sum([FAMTBF]) + Sum([FAMTDB]) + Sum([FAMTCR]) as [FAMTCF],
	Sum([LAMTBF]) + Sum([LAMTDB]) + Sum([LAMTCR]) as [LAMTCF]
Resident BNM_SURVEY
Group By
	Account,
	[Curr.],
	[CTY];

[SCM]

have synthetic error

[jonathandienst]

The synthetic (composite) key is a result of multiple common fields. Synthetic keys are often a warning of a poorly designed data model. In this case, you could leave the synthetic key in place, or add a join instruction ahead of the last load to join the data into the fact table. Be aware that a join will add or remove records, depending on the type of join, and if the data field values are misaligned or contain duplicate values of the composite key.