topic Re: Qvd's from different sources in QlikView

Qvd's from different sources

Anonymous — Fri, 09 Oct 2015 12:17:55 GMT

Hi,

I have 3 qvd's namely dim_account, dim_state and fact_lines.

account qvd is coming from sql server and rest of qvds are coming from oracle data base.

I have imported into QVW file made relationship between fact_lines and as well dim_account with correct pk and fk.

But if i pull any dimension column from account and expressions from fact, i haven't seen any data.

If i do list box for dim_account coulms there is data. is there any way to figure out this issue.

Pls help me on this issue

Regards,

Kumar

Re: Qvd's from different sources

martynlloyd — Fri, 09 Oct 2015 12:22:37 GMT

When you display the data, check if it is right aligned or left aligned.

You may be treating one as a number and the other as text, check for extra spaces etc.

Or export both to Excel and use EXACT() to compare them...

Marty.

Re: Qvd's from different sources

avinashelite — Fri, 09 Oct 2015 12:26:10 GMT

check with your expressions once and the table links . All the linking keys should be in same format .

Hope this helps you

Re: Qvd's from different sources

Anonymous — Fri, 09 Oct 2015 12:31:40 GMT

Hi,

In expressions it is populating values, but in dimensions i couldn;t see any data. There is relation proper relationship between fact and dimensions and i have seen in table viewer.

Re: Qvd's from different sources

Anonymous — Fri, 09 Oct 2015 12:33:47 GMT

I am able to see count date wise, but the problem with account QVD only.

Re: Qvd's from different sources

sudeepkm — Fri, 09 Oct 2015 12:40:25 GMT

what is the subset ratio of your key field in both dim and fact?

Do you have a screenshot or a scrambled sample that you can share?

Re: Qvd's from different sources

Anonymous — Fri, 09 Oct 2015 12:45:14 GMT

For fact table it is 0.05% and for dim 99.95%

Re: Qvd's from different sources

Anonymous — Fri, 09 Oct 2015 12:47:07 GMT

this dim is coming from one source and fact is coming from another source. Is it possible to have two difference in one QVW file.

Re: Qvd's from different sources

sudeepkm — Fri, 09 Oct 2015 12:51:15 GMT

0.05% is too less not sure how many records. but it seems there are bunch of field values not same across both the tables. unless you have matching or associated values your chart wont display anything.

I'm not saying the chart or expression or data is wrong but I think as per your subset ratio the dim to fact association is weak and not have enough amount of matching record.

Re: Qvd's from different sources

petter — Fri, 09 Oct 2015 12:53:37 GMT

The key field - the field that connects your two tables - the dimension table and the fact table, will only match rows between the tables if the values match. So check why the values in the dimension don't match the values in the fact table. The fact table should have 100% subset ratio on the key field that goes to the dimension table.

Is it a date or timestamp in either of the source database columns? Maybe you need to do a Floor() to get the exact granularity of a full day...

Re: Qvd's from different sources

Anonymous — Fri, 09 Oct 2015 12:59:06 GMT

Thanks Sudeep.

Can you answer me on this one.

dim_account is coming from SQl server

and fact_lines is coming from Oracle

We joined with account_key from dim and with fact table column of fact_act_key.

will it pull data, if wejoin like this.

Regards,

Kumar

Re: Qvd's from different sources

sudeepkm — Fri, 09 Oct 2015 13:07:24 GMT

QlikView supports heterogeneous data load in same QVW so no problem in loading data from two sources.

the best way to do is to have individual QVD Gen qvws to create QVD based of the source system tables.

I think you are already doing it.

Next thing is preparing the data model in QlikView for which you need to identify the association key.

In your case the account_key is the key field. Please make sure the field name is same in both the tables.

You can join or you can leave it as it is. as per your tale names it seems like a star schema (dim and fact) and QlikView supports the Star Schema model very well.

Re: Qvd's from different sources

Anonymous — Fri, 09 Oct 2015 13:12:56 GMT

Qvd are coming from individual QVW's only. But fact table the key field is different from dim table. Is it mandatory to be the both key fields name same.

Re: Qvd's from different sources

sudeepkm — Fri, 09 Oct 2015 13:16:50 GMT

QlikView associates two or more tables based on Field name. So as long as the Field names are exactly same the association will work. If you have more than one fields with same name across different tables then you will face synthetic keys which you can avoid by using proper data model techniques.

Re: Qvd's from different sources

Anonymous — Fri, 09 Oct 2015 13:32:18 GMT

Hi Petter,

One is datetime and another table as timestamp without timezone.

Re: Qvd's from different sources

Anonymous — Fri, 09 Oct 2015 13:41:57 GMT

What you said is correct.

dim_account:

Load account key as fact_key

name,

act_no

Fact_lines:

Load fact_lines,

lines_status

Like this I joined.

My joining is correct or not. Can you guide me here.

Re: Qvd's from different sources

Anonymous — Fri, 09 Oct 2015 13:44:33 GMT

Hi Kumar,

Please try as below:

dim_account:

Load account key as fact_key

name,

act_no

Fact_lines:

Load fact_lines as fact_key,(if Fact_lines and Fact_key fields are common ids.)

lines_status

Re: Qvd's from different sources

Anonymous — Fri, 09 Oct 2015 13:50:45 GMT

No, Neetha,

It is not working

Re: Qvd's from different sources

sudeepkm — Fri, 09 Oct 2015 13:54:25 GMT

what is the key field in Fact table that matches (you want to associate) with the key field in Dim table.

Re: Qvd's from different sources

Anonymous — Fri, 09 Oct 2015 16:51:57 GMT

Check for Key field in fact table which match to the Key field in Dimension table and rename to the same name.

if no common field,please post sample data, will be able to help.