topic Re: Group by clause in Load statement in QlikView

Group by clause in Load statement

Wed, 10 Oct 2012 15:36:06 GMT

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.

Re: Group by clause in Load statement

Wed, 10 Oct 2012 16:03:53 GMT

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&List={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}&View={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}] (XmlSimple, Table is [xml/data/row])

GROUP BY SubsectionID, subsectionAddressed;

Re: Group by clause in Load statement

Gysbert_Wassenaar — Wed, 10 Oct 2012 18:04:59 GMT

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&List={XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}&View={XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXX} (XmlSimple, Table is [xml/data/row])

Re: Group by clause in Load statement

Wed, 10 Oct 2012 18:12:27 GMT

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?

Re: Group by clause in Load statement

Gysbert_Wassenaar — Wed, 10 Oct 2012 18:36:52 GMT

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

LOAD

SubsectionID,

count(SubsectionAddressed) as Grouptest

group by SubsectionID;

Re: Group by clause in Load statement

Wed, 10 Oct 2012 19:37:42 GMT

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&List={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}&View={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}] (XmlSimple, Table is [xml/data/row])

GROUP BY SubsectionID

Re: Group by clause in Load statement

Wed, 10 Oct 2012 20:28:28 GMT

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.

Re: Group by clause in Load statement

Wed, 10 Oct 2012 20:41:22 GMT

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&XMLDATA=1&RowLimit=0&List={87CF967A-C288-4882-8B21-578CF322181D}&View={ECA3ECEF-8E78-4DE5-86B3-080F5F08C251}] (XmlSimple, Table is [xml/data/row])

GROUP BY max(ows_SubsectionID)

Re: Group by clause in Load statement

Wed, 10 Oct 2012 20:46:20 GMT

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&XMLDATA=1&RowLimit=0&List={87CF967A-C288-4882-8B21-578CF322181D}&View={ECA3ECEF-8E78-4DE5-86B3-080F5F08C251}] (XmlSimple, Table is [xml/data/row])

GROUP BY ows_SubsectionID

Re: Group by clause in Load statement

Wed, 10 Oct 2012 21:11:25 GMT

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&XMLDATA=1&RowLimit=0&List={87CF967A-C288-4882-8B21-578CF322181D}&View={ECA3ECEF-8E78-4DE5-86B3-080F5F08C251}] (XmlSimple, Table is [xml/data/row])
GROUP BY

ows_SubsectionID

Re: Group by clause in Load statement

Gysbert_Wassenaar — Thu, 11 Oct 2012 07:56:39 GMT

What is the error message?

I see you are now using ows_SubsectionID instead of SubsectionID. Are you sure the ows_* fields exist in the xml table?

And make sure you end the statement with a semicolon ;

Re: Group by clause in Load statement

Thu, 11 Oct 2012 10:05:15 GMT

Try like:

LOAD

SubsectionID,

sum(if(SubsectionAddressed='',1,0))as Grouptest

WHERE SubsectionAddressed = ' '

GROUP BY SubsectionID

Re: Group by clause in Load statement

Thu, 11 Oct 2012 14:27:20 GMT

I tried it with the ; and still recieved a load failure. The original field name is SubsectionID but when i pull it into qlikview through a web file it adds the ows_ in front of all fields.

Re: Group by clause in Load statement

Thu, 11 Oct 2012 14:34:24 GMT

I tried your suggestion as well. Should I not be including (XmlSimple, Table is [xml/data/row]) a the end of the file location? I also recieved a load script failure error message.

Re: Group by clause in Load statement

Tue, 16 Oct 2012 16:16:10 GMT

Does anyone have more insite on this?

I tried with this statement below but keep getting a loadle listed below in my qlikview to bring in other data. Does that matter?

LOAD

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

WHERE ows_SubsectionAddressed = ''
GROUP BY ows_SubsectionID,
ows_SubsectionAddressed

Thank you

Re: Group by clause in Load statement

robert99 — Thu, 18 Oct 2012 07:20:41 GMT

Have you done this (as noted above)

And make sure you end the statement with a semicolon ;

Re: Group by clause in Load statement

Thu, 18 Oct 2012 13:50:09 GMT

I think I figured it out. I switched to using a Resident and then added the ; as well. I need to play with it a little more to get the answers i am looking for but it no longer brings a load script error.

Load

ows_SubsectionID,
sum(if(ows_SubsectionAddressed='',1,0)) as Grouptest
RESIDENT SubsectionWorkstream
//Count(ows_SubsectionAddressed)
//sum(if(ows_SubsectionAddressed='Yes',1,0)) as Grouptest
WHERE ows_SubsectionAddressed = ''
GROUP BY ows_SubsectionID;

Thank you.