Aggregation function not working in preceding load

[Adorable09]

Hi All,

I have  data with 10 columns which I am loading from qvd.In preceding load ,I am trying to calculate count (fieldname ) group by 2 dimensions(not all dimensions) in preceding load .It is returning me only the columns which I am loading in preceding load.Please see below code.

Table1:

Load

Col1 ,Col2,count(Col 5) as TotalCount

group by Col1 ,Col2;

Load Col1,Col2......Col10 from xyz.qvd(qvd);

I am getting only Col1 ,Col2 and TotalCount in the model.I want to load other columns as well.

Can someone please tell what I am missing here?

Thanks.

[stevejoyce · Accepted solution]

That's expected, your result is just what fields are in the preceeding load.

You can do your group by and join it back with your other columns but this may cause duplicate data, in that case you can keep it as a seperate table:

Table1:

Load

Col1 & '|' & Col2 as %Key_Col_1_2

Col1,Col2......Col10 from xyz.qvd(qvd);

Table1_aggrCol1Col2:

Load Distinct

%Key_Col_1_2

,count(Col 5) as TotalCount

resident Table1

group by %Key_Col_1_2;