topic Re: Group By Error in QlikView

Group By Error

Thu, 08 May 2014 15:26:08 GMT

Hi All,

The below script is giving Invalid Express Expressions error. Please let me know how to resolve this?

Speaker_Fact:

LOAD DIM_TIME_ID,
ATTENDEE_TYPE,
PHY_NUMBER,
Count(DIM_SPEAKER_EVENT_ID) as PP_PROGRAM_COUNT,
Count(FACT_SPEAKER_EVENT_MTH_ID) as ATTENDEE_COUNT,
SPEAKER_PROGRAM
FROM
[..\QVD\Speaker_Fact_2.qvd]
(qvd) Group By DIM_TIME_ID ;

Thanks,

Kumar

Re: Group By Error

Thu, 08 May 2014 15:36:09 GMT

Hi,

You either need to:

1) extend your group by clause to include ATTENDEE_TYPE, PHY_NUMBER, SPEAKER_PROGRAM

2) apply an aggregation rule (e.g. count, sum, min, max) to the following fields ATTENDEE_TYPE, PHY_NUMBER, SPEAKER_PROGRAM

3) remove the following fields from your load statement: ATTENDEE_TYPE, PHY_NUMBER, SPEAKER_PROGRAM

Kind regards,

Matthijs

Re: Group By Error

Thu, 08 May 2014 16:11:11 GMT

Matthijs covered it all but just wanna add little bit.

When you use group by all the fields should use some sort of aggregation function (Count, Sum, Only,....) except for the ones that are being group by. So in your case maybe you can do this:

Speaker_Fact:

LOAD DIM_TIME_ID,
Only(ATTENDEE_TYPE) as ATTENDEE_TYPE,
Only(PHY_NUMBER) as PHY_NUMBER,
Count(DIM_SPEAKER_EVENT_ID) as PP_PROGRAM_COUNT,
Count(FACT_SPEAKER_EVENT_MTH_ID) as ATTENDEE_COUNT,
Only(SPEAKER_PROGRAM) as SPEAKER_PROGRAM
FROM
[..\QVD\Speaker_Fact_2.qvd]
(qvd) Group By DIM_TIME_ID ;

Re: Group By Error

Thu, 08 May 2014 16:19:23 GMT

Thanks Mathijis