topic Re: SQL Query convert to Qlikview Script in QlikView

SQL Query convert to Qlikview Script

erric3210 — Fri, 15 Nov 2024 23:45:19 GMT

Hi All,

I have SQL Query with me and referring this I need to build same query in Qlikview using Qlikview Script not same below SQL.

This is something new to me and I can't see Joins in the below SQL Query. I'm referring the below SQL query to build data model script in Qlikview. Here is my SQL Query:

SQL SELECT
AER.CASE_ID,
AER.AER_ID,
AER.VERSION_NO,
AER.AER_INIT_RECV_DATE IRD_ADJ,
UPPER(AER_PRODUCT.TRADE_NAME) TRADE_NAME,
AER_PRODUCT.SEQ_PRODUCT,
AER_REACT.SEQ_REACT
FROM
DIM_CASE AER,
CONFIG_COUNTRY SR_COUNTRY_REGION,
( SELECT CASE_ID, SEQ_PRODUCT, CASE_PRODUCT_ID, PRIMARY_INDICATION_PT_CODE,PRODUCT_FLAG, DIM_PRODUCT.TRADE_NAME, CONFIG_PRODUCT.PRODUCT_GROUP_NAME,
FLAG_RMT
FROM CONFIG_PRODUCT, DIM_PRODUCT
WHERE UPPER(DIM_PRODUCT.TRADE_NAME)=UPPER(CONFIG_PRODUCT.TRADE_NAME ) AND CONFIG_PRODUCT.CONFIG_ID=0
AND FLAG_RMT = 'YES' AND NVL(DIM_PRODUCT.PRODUCT_RECORD_CATEGORY,'Other') IN ( 'Valid Product Record','Other' )
AND CONFIG_PRODUCT.DM_EFFECTIVE_DATE_END = '01-JAN-2099'
) AER_PRODUCT,
( SELECT * FROM CONFIG_INDICATION WHERE IS_ACTIVE = 'YES' ) CONFIG_INDICATION,
DIM_EVENT AER_REACT,
FACT_CASE_PRODUCT_EVENT,
(Select report_parameter1_code product_group_name from sr_report_parameter where parameter_list_type = 'RMT_PRODUCT_OFFSET_FOR IBD') OFFSET
WHERE ( AER.CASE_ID=FACT_CASE_PRODUCT_EVENT.CASE_ID )
AND FACT_CASE_PRODUCT_EVENT.CASE_PRODUCT_ID = AER_PRODUCT.CASE_PRODUCT_ID
AND FACT_CASE_PRODUCT_EVENT.CASE_EVENT_ID = AER_REACT.CASE_EVENT_ID
AND ( SR_COUNTRY_REGION.CONFIG_ID(+)=0 )
AND ( AER.AER_COUNTRY=SR_COUNTRY_REGION.COUNTRY_NAME(+) )
AND AER_PRODUCT.PRIMARY_INDICATION_PT_CODE = CONFIG_INDICATION.INDICATION_PT_CODE (+)
AND AER_PRODUCT.PRODUCT_GROUP_NAME = CONFIG_INDICATION.PRODUCT_NAME (+)
AND AER_PRODUCT.PRODUCT_GROUP_NAME = OFFSET.PRODUCT_GROUP_NAME (+)
AND AER_PRODUCT.PRODUCT_FLAG = 'Suspect'
AND ( AER.latest_aer_version_no = 'YES' and AER.approval_status = 'YES'
AND AER_REACT.SEQ_AE IS NULL
AND AER.aer_case_deleted = 'NO' and AER.aer_deleted = 'NO' );

Need your help guys.

Regards,

Eric

Re: SQL Query convert to Qlikview Script

benvatvandata — Fri, 23 Oct 2020 16:17:45 GMT

Table1: LOAD * ; SQL SELECT AER.case_id, AER.aer_id, AER.version_no, AER.aer_init_recv_date IRD_ADJ, Upper(AER_PRODUCT.trade_name) TRADE_NAME, AER_PRODUCT.seq_product, AER_REACT.seq_react FROM dim_case AER, config_country SR_COUNTRY_REGION, (SELECT case_id, seq_product, case_product_id, primary_indication_pt_code, product_flag, dim_product.trade_name, config_product.product_group_name, flag_rmt FROM config_product, dim_product WHERE Upper(dim_product.trade_name) = Upper(config_product.trade_name) AND config_product.config_id = 0 AND flag_rmt = 'YES' AND Nvl(dim_product.product_record_category, 'Other') IN ( 'Valid Product Record', 'Other' ) AND config_product.dm_effective_date_end = '01-JAN-2099') AER_PRODUCT, (SELECT * FROM config_indication WHERE is_active = 'YES') CONFIG_INDICATION, dim_event AER_REACT, fact_case_product_event, (SELECT report_parameter1_code product_group_name FROM sr_report_parameter WHERE parameter_list_type = 'RMT_PRODUCT_OFFSET_FOR IBD') OFFSET WHERE ( AER.case_id = fact_case_product_event.case_id ) AND fact_case_product_event.case_product_id = AER_PRODUCT.case_product_id AND fact_case_product_event.case_event_id = AER_REACT.case_event_id AND ( SR_COUNTRY_REGION.config_id(+) = 0 ) AND ( AER.aer_country = SR_COUNTRY_REGION.country_name(+) ) AND AER_PRODUCT.primary_indication_pt_code = CONFIG_INDICATION.indication_pt_code (+) AND AER_PRODUCT.product_group_name = CONFIG_INDICATION.product_name (+) AND AER_PRODUCT.product_group_name = OFFSET.product_group_name (+) AND AER_PRODUCT.product_flag = 'Suspect' AND ( AER.latest_aer_version_no = 'YES' AND AER.approval_status = 'YES' AND AER_REACT.seq_ae IS NULL AND AER.aer_case_deleted = 'NO' AND AER.aer_deleted = 'NO' );

Re: SQL Query convert to Qlikview Script

erric3210 — Sat, 24 Oct 2020 18:53:32 GMT

Hi benvatvandata,

Thanks for the reply & sharing this amazing code. I really appreciate your help. It's a new learning for me.

Sorry, I haven't explain it well. What I'm trying to achieve here & what exactly my requirement is:

1): Firstly, I stored individual tables from above SQL query with required fields in a qvd format.

2): Secondly, I tried to join other tables which are in the SQL query with where conditions.

Now, the problem is I don't know which join I need to use in Qlikview because referring SQL query I'm not able to find out that which join is used in it.

Sorry again for not explaining my requirement correctly.

Regards,

Eric

Re: SQL Query convert to Qlikview Script

benvatvandata — Mon, 26 Oct 2020 16:55:10 GMT

No problem, could you share your script where you stored the individual tables? Or do you think you may need to revise that part too?

Re: SQL Query convert to Qlikview Script

benvatvandata — Mon, 26 Oct 2020 17:25:50 GMT

Also... if you're able to query the database... could you let me know if the below query gives the same results as the query you initially posted?

SELECT aer.case_id, aer.aer_id, aer.version_no, aer.aer_init_recv_date ird_adj, Upper(aer_product.trade_name) trade_name, aer_product.seq_product, aer_react.seq_react FROM dim_case AER join fact_case_product_event ON AER.case_id = fact_case_product_event.case_id join (SELECT case_id, seq_product, case_product_id, primary_indication_pt_code, product_flag, dim_product.trade_name, config_product.product_group_name, flag_rmt FROM config_product, dim_product WHERE Upper(dim_product.trade_name) = Upper( config_product.trade_name) AND config_product.config_id = 0 AND flag_rmt = 'YES' AND Nvl(dim_product.product_record_category, 'Other') IN ( 'Valid Product Record', 'Other' ) AND config_product.dm_effective_date_end = '01-JAN-2099') AER_PRODUCT ON fact_case_product_event.case_product_id = AER_PRODUCT.case_product_id join dim_event AER_REACT ON fact_case_product_event.case_event_id = AER_REACT.case_event_id join config_country SR_COUNTRY_REGION ON AER.aer_country = SR_COUNTRY_REGION.country_name(+) join (SELECT * FROM config_indication WHERE is_active = 'YES') CONFIG_INDICATION ON AER_PRODUCT.primary_indication_pt_code = CONFIG_INDICATION.indication_pt_code (+) AND AER_PRODUCT.product_group_name = CONFIG_INDICATION.product_name (+) join (SELECT report_parameter1_code product_group_name FROM sr_report_parameter WHERE parameter_list_type = 'RMT_PRODUCT_OFFSET_FOR IBD') OFFSET ON AER_PRODUCT.product_groupname = OFFSET.product_group_name (+) WHERE sr_country_region.config_id(+) = 0 AND aer_product.product_flag = 'Suspect' AND aer.latest_aer_version_no = 'YES' AND aer.approval_status = 'YES' AND aer_react.seq_ae IS NULL AND aer.aer_case_deleted = 'NO' AND aer.aer_deleted = 'NO'

Re: SQL Query convert to Qlikview Script

erric3210 — Fri, 30 Oct 2020 08:06:34 GMT

Hi Sir,

First of all, Sir Thank you so much for devoting your time on my Post. I really appreciate that.

This query giving below error:

QL##f - SqlState: S1000, ErrorCode: 25156, ErrorMsg: [Oracle][ODBC][Ora]ORA-25156: old style outer join (+) cannot be used with ANSI joins

The approach I followed is that:

dim_case:

SELECT *
FROM dim_case;

STORE dim_case into my location\dim_case.qvd(qvd);

DROP TABLE dim_case;

dim_product:

SELECT *
FROM dim_product;

STORE dim_product into my location\dim_product.qvd(qvd);

DROP TABLE dim_product;

same I did for all the tables involved in SQL Query. Stored all of them in a qvd format and after getting all individual tables i'm trying to connect them using joins and finally creating data model out of it.

Sir, one question which is bothering me, How did you find out from the above SQL query that this is a join and not inner join, left join or any other type of join. In SQL query, there is nowhere joins are used. How query work and joins work in that case. I need to understand that part at the time of joining my new Model while referring the SQL query.

Regards,

Eric

Re: SQL Query convert to Qlikview Script

erric3210 — Fri, 30 Oct 2020 08:08:02 GMT

Hi Sir,

This query is giving me below error:

QVX_UNEXPECTED_END_OF_DATA: SQL##f - SqlState: 08S01, ErrorCode: 3113, ErrorMsg: [Oracle][ODBC][Ora]ORA-03113: end-of-file on communication channel

This error i'm getting from your first reply in which you've customized SQl Query.

Regards,

Eric

Re: SQL Query convert to Qlikview Script

benvatvandata — Mon, 02 Nov 2020 17:51:20 GMT

ahh my guess is those (+) signs are causing the issue... i've never really used those joins before (was just trying my best to use your code, but joining in different way)... what about if you just try:

SELECT aer.case_id, aer.aer_id, aer.version_no, aer.aer_init_recv_date ird_adj, Upper(aer_product.trade_name) trade_name, aer_product.seq_product, aer_react.seq_react FROM dim_case AER join fact_case_product_event ON AER.case_id = fact_case_product_event.case_id join (SELECT case_id, seq_product, case_product_id, primary_indication_pt_code, product_flag, dim_product.trade_name, config_product.product_group_name, flag_rmt FROM config_product, dim_product WHERE Upper(dim_product.trade_name) = Upper( config_product.trade_name) AND config_product.config_id = 0 AND flag_rmt = 'YES' AND Nvl(dim_product.product_record_category, 'Other') IN ( 'Valid Product Record', 'Other' ) AND config_product.dm_effective_date_end = '01-JAN-2099') AER_PRODUCT ON fact_case_product_event.case_product_id = AER_PRODUCT.case_product_id join dim_event AER_REACT ON fact_case_product_event.case_event_id = AER_REACT.case_event_id join config_country SR_COUNTRY_REGION ON AER.aer_country = SR_COUNTRY_REGION.country_name join (SELECT * FROM config_indication WHERE is_active = 'YES') CONFIG_INDICATION ON AER_PRODUCT.primary_indication_pt_code = CONFIG_INDICATION.indication_pt_code AND AER_PRODUCT.product_group_name = CONFIG_INDICATION.product_name join (SELECT report_parameter1_code product_group_name FROM sr_report_parameter WHERE parameter_list_type = 'RMT_PRODUCT_OFFSET_FOR IBD') OFFSET ON AER_PRODUCT.product_groupname = OFFSET.product_group_name WHERE sr_country_region.config_id = 0 AND aer_product.product_flag = 'Suspect' AND aer.latest_aer_version_no = 'YES' AND aer.approval_status = 'YES' AND aer_react.seq_ae IS NULL AND aer.aer_case_deleted = 'NO' AND aer.aer_deleted = 'NO'

Sorry been a busy week, I'll try to look into joining the qvds when I have some free time this week. I wanted to know if your original query produced the same results as the new query to validate if they are inner joins/outer joins/etc. (since I haven't used the '(+)' types of joins before)

Re: SQL Query convert to Qlikview Script

benvatvandata — Wed, 04 Nov 2020 01:25:35 GMT

Just curious... is there a reason why you need to store each table as a qvd?

Are you planning on building a lot more on top of this?

Could you share your full load script for storing the qvd's? I think I could create your load script without too much difficulty, but I need some clarification so that I don't write code and then have to scrap it all because one little bit of information ruins the whole plan.

Re: SQL Query convert to Qlikview Script

Brett_Bleess — Wed, 04 Nov 2020 19:04:42 GMT

@erric3210 Just going to toss out the Help Doc on QlikView Join as well, it covers all the different types, hopefully that may help a little as well. You can find some further example related items in the Design Blog area too, so I will leave a link for that as well, but the partner seems to be taking great care of you one this one, I just wanted to toss in this other info as well for others in the future too.

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptPrefixes/Join.htm

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

Re: SQL Query convert to Qlikview Script

erric3210 — Wed, 04 Nov 2020 20:57:30 GMT

Hi benvatvandata,

Sir, reason for storing is just to test out all the possibilities related to memory load, load time, performance. But the main purpose it to understand how joins are working in SQL (maybe that is the Inner Join in the Query above). I'm trying to replicate the same SQL script in Qlikview and out of that want to generate same result with SQL query as well Qlikview Script.

The new updated query which you've shared, I need to test it out. I'll share the results with you.

Yes, sir there are 4-5 more queries similar to that but those are master tables queries so no issue in that.

You're absolutely right Sir.

What I'm trying to achieve here is:

1): First, I'm creating a Document.

2): Secondly, I'm finding how many tables are used in the SQL Query. Documenting all those table Names and used fields from in it.

3): Understanding the Data by extracting the tables individually.

4): Then, storing all the used tables individually in my system Folder using Qlikview Store command. Qvd's concept is ausm.

5): After this, I try to understand the joining keys, cardinality, relationships b/w tables. What can be done to increase the performance at the time of joining or any other way so that data will not duplicate, data discrepancy should not come, performance will not degrade. Where ever required I try to use Hash Keys, Auto number or creating composite keys.

I'm in a learning phase and you really helped me out. Really appreciate that.

I'm doing this for the better performance, speed & optimization.

Regards,

Eric

Re: SQL Query convert to Qlikview Script

erric3210 — Wed, 04 Nov 2020 21:00:53 GMT

Hi Brett Sir,

Thank you for sharing those lovely links. Some doubts are cleared after going through those links.

Design blog was really ausm. So much power packed content.

Thanks again for sharing those links.

Regards,

Eric