Converting SQL query into Qlikview

[#1]

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%';

[phaneendra_kunc · Accepted solution]

//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%';

[#3]

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

Chris