Data model for a query

[#1]

Hi All,

PFA sql , there are two queries sub queries which are "unioned" , I had to do a data model for this, I thought of creating a qvd for each of the sub queries and finally doing a "Concatenate" but I am insisted not create a qvd for the sub queries.

The problem I am facing is both the sub queries are using same tables except two joins conditions are changing .

Any help on this

hic‌

gwassenaar‌

stalwar1‌

marcus_sommer‌

[MK_QSL]

What's the issue coming when you are using this query in QlikView directly in load script?

[PrashantSangle]

Hi,

take all tables only once due to associative nature of qlikview it wont make any issue.

Regards

[#4]

There are no issues with the SQL, actually it is a part of View definition, we generally don't use the SQL to build a qvd

thats all

[#5]

Prashanth

if you observe the where clauses of both of the query in the attached file there is some difference , if I take all the tables only once , how can I handle that where and also the union in between them

[#6]

Attaching a PIC of the difference in where conditions, please suggest any alternatives

[parimikittu]

Hi Kumar,

I have worked on similar Pl/Sql to Qlik conversion. My Approach is

1) First Load all the used tables.

2) Create mapping tables if PL/Sql tables are joined to get only single column.

3) Write logic for script above the union

4) Then write logic for the script below union and concatenate distinst with the step 3

all in single qvw. Don't create seperate qvd as it have to load all the necessary tables twice.

[PrashantSangle]

Hi,

use join for that.

Left join your dimensional table to your fact table and avoid this issue.

Regards

[#9]

Thanks Prashanth and vk Kri,

Vk Kri:

I have nearly 10 tables in each query above and below Union and cannot create a data model for all of them as we have to join some of the tables, but all of them are OLTP tables and more over I have 17 such subqueries , in the file I have listed only 2 of them

Prashanth:

All are OLTP tables, I think I have to check in this way.

[parimikittu]

In the file, Both the queries are same except the idenified joins, So, We have to create a table with rest of the joins excluding the

AND trx.ship_to_site_use_id = su.site_use_id

AND trx.ship_to_customer_id = ac.customer_id

AND trx.bill_to_site_use_id = su.site_use_id

AND trx.bill_to_customer_id = ac.customer_id

Once a table with joins is created(Call it TAB). We have to join based on these

AND trx.ship_to_site_use_id = su.site_use_id

AND trx.ship_to_customer_id = ac.customer_id

and form TAB1

and then join TAB with

AND trx.bill_to_site_use_id = su.site_use_id

AND trx.bill_to_customer_id = ac.customer_id

and Call it TAB2.

Then concatenate distinct on TAB1 and TAB2.

If rest of the subqueries are different, Then we either need to the understand the prupose of the Pl/SQL and desgin a App from scratch without looking into the PL/SQL and with help of Business users or replicate the PL/SQL