Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
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])
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?
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;
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
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.
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)
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
Tried MAX and Count. Both times exicution of script failed....
LOAD
ows_SubsectionID