topic Re: Aggregate the data set in QlikView

Aggregate the data set

katetsan — Wed, 07 Mar 2018 02:28:02 GMT

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

;

Re: Aggregate the data set

binujose1982 — Wed, 07 Mar 2018 04:04:21 GMT

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

Re: Aggregate the data set

Digvijay_Singh — Wed, 07 Mar 2018 04:05:31 GMT

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]

;

Re: Aggregate the data set

niclaz79 — Wed, 07 Mar 2018 08:15:30 GMT

Hi,

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