topic Converting SQL query into Qlikview in QlikView

Converting SQL query into Qlikview

Tue, 12 Feb 2013 21:36:33 GMT

Hi,

I am new to Qlikview and I am trying to recreate an SQL query that I have into Qlikview and I am having a few issues. Any help would be appreciated.

Thanks,

Chris

------------------------------SQL Query--------------------------------------------

SELECT lg.event_timestamp, let.detail_text from log_event lg

inner join log_event_detail let on (lg.log_event_id=let.log_event_id)

where lg.component_name like 'CIMSPublishToMDMMainFlow%' and

to_char(lg.event_timestamp,'yyyy-mm-dd') = to_char(CURRENT_DATE -1,'yyyy-mm-dd') and

let.detail_text like 'BIP%'

-----------------my Qlikview Load Script-------------------------------------------------------

LogEventData:

LOAD "LOG_EVENT_ID",

"COMPONENT_NAME",

"EVENT_TIMESTAMP";

SQL SELECT "LOG_EVENT_ID",

"COMPONENT_NAME",

"EVENT_TIMESTAMP"

FROM "PECOAMI_DATA"."LOG_EVENT"

WHERE COMPONENT_NAME like 'CIMSPublishToMDMMainFlow%'

AND to_char(event_timestamp,'yyyy-mm-dd') = to_char(CURRENT_DATE -1,'yyyy-mm-dd');

INNER JOIN

LogEventDetail:

LOAD "LOG_EVENT_DETAIL_ID",

"LOG_EVENT_ID",

"DETAIL_TEXT";

SQL SELECT "LOG_EVENT_DETAIL_ID",

"LOG_EVENT_ID",

"DETAIL_TEXT"

FROM "PECOAMI_DATA"."LOG_EVENT_DETAIL"

WHERE DETAIL_TEXT like 'BIP%';

Re: Converting SQL query into Qlikview

phaneendra_kunc — Tue, 12 Feb 2013 22:05:00 GMT

//Option 1

LogEventData:

Load *;

SELECT lg.event_timestamp, let.detail_text from log_event lg

inner join log_event_detail let on (lg.log_event_id=let.log_event_id)

where lg.component_name like 'CIMSPublishToMDMMainFlow%' and

to_char(lg.event_timestamp,'yyyy-mm-dd') = to_char(CURRENT_DATE -1,'yyyy-mm-dd') and

let.detail_text like 'BIP%' ;

//Option 2:

LogEventData:

LOAD "LOG_EVENT_ID",

"COMPONENT_NAME",

"EVENT_TIMESTAMP";

SQL SELECT "LOG_EVENT_ID",

"COMPONENT_NAME",

"EVENT_TIMESTAMP"

FROM "PECOAMI_DATA"."LOG_EVENT"

WHERE COMPONENT_NAME like 'CIMSPublishToMDMMainFlow%'

AND to_char(event_timestamp,'yyyy-mm-dd') = to_char(CURRENT_DATE -1,'yyyy-mm-dd');

INNER JOIN (LogEventData)

LOAD "LOG_EVENT_DETAIL_ID",

"LOG_EVENT_ID",

"DETAIL_TEXT";

SQL SELECT "LOG_EVENT_DETAIL_ID",

"LOG_EVENT_ID",

"DETAIL_TEXT"

FROM "PECOAMI_DATA"."LOG_EVENT_DETAIL"

WHERE DETAIL_TEXT like 'BIP%';

Re: Converting SQL query into Qlikview

Tue, 12 Feb 2013 22:14:18 GMT

Thank you so much, they both worked. The first one loaded significantly faster though.

Chris