Aggregate the data set

[katetsan]

Hi Communities,

Is there anyway to aggregated the data set and reduce the number of fields?

I've tried the group by statement after resident table. However, It shows the invalid expression messgage.

I'm wondering if there's anyway to reach that?

The script is as below. Thanks in advanced.

[BSEG_TEMP]:

Load

    BUKRS as [Company ID], // Company ID

     BELNR as [Accounting Document ID],

    GJAHR as [Accounting Year],

    Num#(ApplyMap('Map_BKPF', BUKRS & '|' & BELNR & '|' & GJAHR, Null()),'##') as Month,

    BUZEI as [Accounting Document Item], // Accounting Document Item Number

    SHKZG as [DC Flag],

    DMBTR as [Amount],

    PSWSL as [Currency],

    HKONT as [Accounting Code],

    PRCTR as [Profit Centre Code],

    KOSTL as [Cost Centre Code],

    ApplyMap('Map_CSKT', KOSTL, Null()) as [Cost Centre Text],

    ApplyMap('Map_CSKS', KOSTL, Null()) as [Object Code]   

From [$(vSavePath)BSEG.qvd](qvd);

[BSEG]:

Load

    [Company ID],

    [Accounting Document ID],

    [Accounting Year],

    Month,

    [DC Flag],

    [Accounting Code],

    [Cost Centre Code],

    [Cost Centre Text],

    [Object Code],

    sum(ApplyMap('COSS_COSP',[Object Code] & '|' & [Accounting Code] & '|' & [DC Flag] & '|'& [Accounting Year] & Month, Null())) as Amount_2

Resident [BSEG_TEMP]

Group by [Object Code], [Accounting Code],[Accounting Year], Month

;

[binujose1982]

Hi Kate,

Please give Noconcatenate statement before resident table Load keyword.

[BSEG]:

Noconcatenate

Load

    [Company ID] ....

.....

Resident [BSEG_TEMP]

Group by [Object Code], [Accounting Code],[Accounting Year], Month

Regards,

Binu

[Digvijay_Singh]

You need to mention all the fields after Group By which are not surrounded by Aggregationn function. So all the fields from Company ID to Object Code nees to be mentioned after Group By.

BSEG]:

Load

    [Company ID],

    [Accounting Document ID],

    [Accounting Year],

    Month,

    [DC Flag],

    [Accounting Code],

    [Cost Centre Code],

    [Cost Centre Text],

    [Object Code],

    sum(ApplyMap('COSS_COSP',[Object Code] & '|' & [Accounting Code] & '|' & [DC Flag] & '|'& [Accounting Year] & Month, Null())) as Amount_2

Resident [BSEG_TEMP]

Group by

[Company ID],

    [Accounting Document ID],

    [Accounting Year],

    Month,

    [DC Flag],

    [Accounting Code],

    [Cost Centre Code],

    [Cost Centre Text],

    [Object Code]

;

[niclaz79]

Hi,

Digvijay is right, you need to group by all columns that are not being aggregated.