topic Can this script work with a resident table? in QlikView

Can this script work with a resident table?

m_woolf — Mon, 27 Jun 2011 12:48:49 GMT

The script below works fine when EVENTSLOG is a database table.

Can this code be modified to work when EVENTSLOG is a resident table?

SQL SELECT Count(EVENTID) AS CountOfEVENTID, EVENTSLOG_1.EVENTID

FROM EVENTSLOG, EVENTSLOG AS EVENTSLOG_1

WHERE (((STARTTIME)>=[EVENTSLOG_1.STARTTIME] And (STARTTIME)<[EVENTSLOG_1.ERRORTIME] And [EVENTSLOG_1.ERRORTIME] Is Not Null) AND ((USERNAME)=[EVENTSLOG_1.USERNAME]))

GROUP BY EVENTSLOG_1.EVENTID;

Can this script work with a resident table?

jdf — Mon, 27 Jun 2011 18:56:16 GMT

mwoolf,

No, SQL can only be used in QlikView scripting when connecting to a SQL database. It is not QlikView that is using the SQL, it is sending the SQL to the database and brining back the table. QlikView language can handle what you are trying to do, but it would need to be translated to QlikView scripting.

Let me know if you have any questions.

Jacob

Can this script work with a resident table?

m_woolf — Mon, 27 Jun 2011 19:00:50 GMT

Jacob,

Thanks for your reply.

I guess my questions would be better phrased as "What changes are required to make this script work for a resident table".

I have tried:

LOAD Count(EVENTID) AS CountOfEVENTID, EVENTSLOG_1.EVENTID

Resident EVENTSLOG, EVENTSLOG AS EVENTSLOG_1

WHERE (((STARTTIME)>=[EVENTSLOG_1.STARTTIME] And (STARTTIME)<[EVENTSLOG_1.ERRORTIME] And [EVENTSLOG_1.ERRORTIME] Is Not Null) AND ((USERNAME)=[EVENTSLOG_1.USERNAME]))

GROUP BY EVENTSLOG_1.EVENTID;

QlikView says it can't find the field "EVENTSLOG_1.EVENTID"

Can this script work with a resident table?

jdf — Mon, 27 Jun 2011 19:03:45 GMT

Can you upload your .qvw so I can work with it?

Can this script work with a resident table?

m_woolf — Mon, 27 Jun 2011 19:09:11 GMT

I would have to also include the data, as the problem involves reloading. The data is confidential and quite large, so I'm afraid not.

Thanks for the offer.

Can this script work with a resident table?

Mon, 27 Jun 2011 19:09:45 GMT

I am assuming you already have the data from the database in a resident table? in a script something like this?

event_log:

sql select eventid

from dbo.eventslog;

Now just change your SQL Select to a Load and your from to a resident:

event_summary:

load count(eventid) as countofeventid,

eventid

resident eventslog

where ...

group by ...;

Can this script work with a resident table?

Mon, 27 Jun 2011 19:17:55 GMT

QlikView says it can't find the field "EVENTSLOG_1.EVENTID"

Unless EVENTSLOG_1.EVENTID exists as a field in the EVENTSLOG table QV wont be able to find it.

It sounds like maybe you are trying to join multiple event log tables together? Could you post a bit more of your load script so we can see better what you are trying to do?

Can this script work with a resident table?

m_woolf — Mon, 27 Jun 2011 19:29:21 GMT

I have a resident table named EVENTSLOG.

If I have the same table loaded from a database, I can use the script:

SQL SELECT Count(EVENTID) AS CountOfEVENTID, EVENTSLOG_1.EVENTID

FROM EVENTSLOG, EVENTSLOG AS EVENTSLOG_1

WHERE (((STARTTIME)>=[EVENTSLOG_1.STARTTIME] And (STARTTIME)<[EVENTSLOG_1.ERRORTIME] And [EVENTSLOG_1.ERRORTIME] Is Not Null) AND ((USERNAME)=[EVENTSLOG_1.USERNAME]))

GROUP BY EVENTSLOG_1.EVENTID;

I'm not the king of SQL, but I believe that this script is joining the EVENTSLOG table to a copy of itself (EVENTSLOG_1) using the Where clause to decide the conditions of the join,

I tried your code and received the message that it couldn't find the field: event_summary.STARTTIME (in the Where clause).

I'm going to work around this problem by having my database people create a view containingthe EVENTLOGS table instead of trying to build it myself in QlikView as a resident table.

Thanks so much for you replies.