topic Re: Data model for a query in QlikView

Data model for a query

Mon, 02 Jan 2017 10:06:48 GMT

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‌

Re: Data model for a query

MK_QSL — Mon, 02 Jan 2017 10:16:33 GMT

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

Re: Data model for a query

PrashantSangle — Mon, 02 Jan 2017 10:36:17 GMT

Hi,

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

Regards

Re: Data model for a query

Mon, 02 Jan 2017 10:42:12 GMT

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

Re: Data model for a query

Mon, 02 Jan 2017 10:43:43 GMT

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

Re: Data model for a query

Mon, 02 Jan 2017 11:05:38 GMT

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

Re: Data model for a query

parimikittu — Mon, 02 Jan 2017 11:24:56 GMT

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.

Re: Data model for a query

PrashantSangle — Mon, 02 Jan 2017 11:27:25 GMT

Hi,

use join for that.

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

Regards

Re: Data model for a query

Mon, 02 Jan 2017 11:35:59 GMT

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.

Re: Data model for a query

parimikittu — Mon, 02 Jan 2017 12:33:37 GMT

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