topic Help with Grouping in Script in QlikView

Help with Grouping in Script

pkelly — Thu, 21 Jan 2010 16:35:35 GMT

Hi...

I have an SQL table which has multiple entries for accounts which equal system login dates with the dates.

I want to create a QVD table from this table detailing the last lagin date for each account (this is then going to be appended to the master account table which has one entry per account)

The code I am using (with no success) is...

TempAccessLogPage01:

LOAD

UPPER("account") AS account,

MAX(creationdate) AS creationdate;

SQL SELECT

account,

creationdate

FROM PublicWebData.dbo."AccessLog_tbl"

GROUP BY account

WHERE success = 1

AND internaladdress = 0

AND impersonate = 0;

Any help on where I am going wrong greatly appreciated...

Help with Grouping in Script

hector — Thu, 21 Jan 2010 16:50:43 GMT

Hi, you forgot the group by in the load, try this:


LOAD
UPPER("account") AS account,
MAX(creationdate) AS creationdate
group by
account;
SQL SELECT....

rgds

Help with Grouping in Script

pkelly — Thu, 21 Jan 2010 17:01:20 GMT

Thanks Hector

Wrongly thought grouping had to be done on sql side...

Regards

Paul

Help with Grouping in Script

hector — Thu, 21 Jan 2010 17:10:21 GMT

Hi, it can be done in the sql side, but the sintax is this:


TempAccessLogPage01:
LOAD
account,
creationdate;
SQL SELECT
account,
max(creationdate) as creationdate
FROM PublicWebData.dbo."AccessLog_tbl"
GROUP BY account
WHERE success = 1
AND internaladdress = 0
AND impersonate = 0

with this way, the SQL Server does the job of grouping instead of QV.

rgds