Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Group by clause in Load statement

Hi,

I am trying to use a group by clause in QLikview 10.  I have this so far, but keep getting a load script failure.

LOAD

     SubsectionID,

     Count(SubsectionAddressed)as Grouptest

     

FROM (XmlSimple, Table is )

WHERE SubsectionAddressed = ' '

GROUP BY SubsectionID

  Could this be occuring because of where i am loading this from? Sharepointpage?  When I show the field information for SubsectionAddressed it bring up blanks or YES which is why I am looking for blanks ' '.

Thank you in advance.

16 Replies
Not applicable
Author

I started to use the resident function and now it says it can not find the table.  Perhaps the way i am pulling the table is wrong?

LOAD

SubsectionID,
(
SubsectionAddressed)as Grouptest

resident [http://sharepointreporting.com/sites/compliance/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0...{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}&View={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}] (XmlSimple, Table is [xml/data/row])

GROUP BY SubsectionID, subsectionAddressed;

Gysbert_Wassenaar

Does it work without the group by and where clauses?

LOAD

     SubsectionID,

     SubsectionAddressed

FROM http://sharepointreporting.com/sites/compliance/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0... (XmlSimple, Table is [xml/data/row])


talk is cheap, supply exceeds demand
Not applicable
Author

Yes,  I used your script, and commented out the Group BY GROUP BY SubsectionID, subsectionAddressed; 

I guess i am using the wrong Group BY fields?

Gysbert_Wassenaar

Ok, that's good. Now try adding the group by statement:

LOAD

     SubsectionID,

     count(SubsectionAddressed) as Grouptest

FROM http://sharepointreporting.com/sites/compliance/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0... (XmlSimple, Table is [xml/data/row])

group by SubsectionID;


talk is cheap, supply exceeds demand
Not applicable
Author

Hi,

I recieved this:

Aggregation expressions required by GROUP BY clause
LOAD
SubsectionID,
(SubsectionAddressed)as Grouptest

From [http://sharepointreporting.om/sites/compliance/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&...{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}&View={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}] (XmlSimple, Table is [xml/data/row])

GROUP BY SubsectionID

Not applicable
Author

I believe you need to change the second field from "(SubsectionAddressed)" to using an aggregation function like "max(SubsectionAddressed)".  Its the same as in SQL.

Not applicable
Author

unfortunatly that did not work.  I tried maxstring, min, and a few others but still got this error:

Aggregation expressions required by GROUP BY clause
LOAD
ows_SubsectionID,
(ows_SubsectionAddressed)as Grouptest

From [http://sharepointreportingprd.gcm.com/sites/compliance/RuleTracker/_vti_bin/owssvr.dll?Cmd=Display&X...{87CF967A-C288-4882-8B21-578CF322181D}&View={ECA3ECEF-8E78-4DE5-86B3-080F5F08C251}] (XmlSimple, Table is [xml/data/row])

GROUP BY max(ows_SubsectionID)

Not applicable
Author

The aggregation should be on the selected field rather than on the GROUP BY clause:

LOAD
ows_SubsectionID,
MAX(ows_SubsectionAddressed)as Grouptest

From [http://sharepointreportingprd.gcm.com/sites/compliance/RuleTracker/_vti_bin/owssvr.dll?Cmd=Display&X...{87CF967A-C288-4882-8B21-578CF322181D}&View={ECA3ECEF-8E78-4DE5-86B3-080F5F08C251}] (XmlSimple, Table is [xml/data/row])

GROUP BY ows_SubsectionID

Not applicable
Author

Tried MAX and Count. Both times exicution of script failed....

 

LOAD


ows_SubsectionID,
MAX(ows_SubsectionAddressed)as Grouptest
From [http://sharepointreportingprd.gcm.com/sites/compliance/RuleTracker/_vti_bin/owssvr.dll?Cmd=Display&X...{87CF967A-C288-4882-8B21-578CF322181D}&View={ECA3ECEF-8E78-4DE5-86B3-080F5F08C251}] (XmlSimple, Table is [xml/data/row])
GROUP BY

ows_SubsectionID