Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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 you upload your .qvw so I can work with it?
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.
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 ...;
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?
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.