topic two linked fields in QlikView

two linked fields

Tue, 26 Apr 2011 10:13:36 GMT

hello,

I've got a litle pb with two fields:

I had a circular reference so I copy out a dimension.

My pb is that I want to compare my two fact tables when they have the same value for "Annee"..

how can i do : when I click on one of fields the other one is selecting if the value is the same ?

thanks

sorry for my english

two linked fields

IAMDV — Tue, 26 Apr 2011 10:26:34 GMT

QlikView solves the problem of circular references by defining a loosely coupled table. QlikView will attempt to make the longest table loosely coupled. To avoid ciruclar references and loosely coupled tables, you must rename the fields that causes the loops, or you can join the tables together into one table to solve the ciruclar reference.

In your case you can join the tables in the load script to fix the problem. Please post the QV document with sample data, incase you need help on doing this task.

Cheers!

two linked fields

Tue, 26 Apr 2011 13:45:52 GMT

I load my dataset from a mysql DB.

So, in the script I have:

ODBC CONNECT TO mysql_local;

SQL SELECT *

FROM etl4.affiliation;

SQL SELECT *

FROM etl4.annee;

SQL SELECT *

FROM etl4.annee2;

SQL Select * .....

So I have to join the two tables in BOLD... What is the syntaxe?

two linked fields

pat_agen — Tue, 26 Apr 2011 14:04:25 GMT

hi,

qlikview will join tables if they have common field names. your nspahot in your firs tpost has the field Année 1 and Annee 2. These names are different so qlikview will not join them.

However you did say that you had a circular reference so "copied out a dimension". It is not clear from your second post what your original problem was. You also mention the requirement of joing two fact tables. Are thes the tables called etl4.annee and etl4.annee2?

If your fields have different names in your original source tables you can rename them as you load the data into qlikview.

two linked fields

Tue, 26 Apr 2011 16:20:16 GMT

Thanks for your ideas but I'm not allowed to modify the names of tables in mysql.

In QV i d'ont know how to modify that..

there is no way to do : when i click on a field I do blablabl with a trigger for example?

two linked fields

IAMDV — Tue, 26 Apr 2011 16:38:07 GMT

You can rename the field names by using AS keyword. You don't have to change the DB schema.

OldFieldName AS NewFieldName,

Hope this helps!

Cheers

two linked fields

IAMDV — Tue, 26 Apr 2011 16:38:35 GMT

Please rename the fields in QV and not in DB.

two linked fields

Wed, 27 Apr 2011 09:42:15 GMT

please can you tell me how to rename the field in QV??

two linked fields

Wed, 27 Apr 2011 09:47:40 GMT

table_name:
load
old_name as new_name,
bad_name as good_name
from your_database

two linked fields

Wed, 27 Apr 2011 11:23:15 GMT

i tried to rename the field in QV, the script works but it tells me that I have a circular reference...

becauses my two facts table are linked to same dimensions... I need to link the two fields but not into the script...

possible?

two linked fields

IAMDV — Wed, 27 Apr 2011 11:40:29 GMT

Is it possible to share the QV document with some sample data? That will be easy to fix the issue...

Cheers!

two linked fields

Wed, 27 Apr 2011 12:31:42 GMT

thanks to take time to answer..

In the document there is the page which is a Pb..

I want to link the filed annee1 with the annee2... because the grid charges is linked to annee1 and the field insertion is linked to annee2 AND I want to link the 2 fact tables..

two linked fields

IAMDV — Wed, 27 Apr 2011 12:59:58 GMT

LOAD

id_annee,

id_periode,

(id_annee2 & '-' & id_periode2) AS UniqueID,

valueur_annee;

SQL SELECT *
FROM etl4.annee;

LOAD

id_annee2,

id_periode2,

(id_annee2 & '-' & id_periode2) AS UniqueID,

valueur_annee2;

SQL SELECT *
FROM etl4.annee2;

Please use the above script and replace them with(Lines 15 - 18 in example file)

SQL SELECT *
FROM etl4.annee;
SQL SELECT *
FROM etl4.annee2;

I can't test it to see if this helps. Because it is fetching the data from MySQL DB directly. So please let me know if this helps...

Good luck!

two linked fields

IAMDV — Wed, 27 Apr 2011 13:00:59 GMT

LOAD

id_annee,

id_periode,

(id_annee & '-' & id_periode) AS UniqueID,

valueur_annee;

SQL SELECT *
FROM etl4.annee;

LOAD

id_annee2,

id_periode2,

(id_annee2 & '-' & id_periode2) AS UniqueID,

valueur_annee2;

SQL SELECT *
FROM etl4.annee2;

Sorry, please ignore the above script and use this one...

two linked fields

Wed, 27 Apr 2011 13:37:40 GMT

when I do that QV tells me I''ve got a cricular reference and it disablesone of the fact tables...

two linked fields

IAMDV — Wed, 27 Apr 2011 13:51:26 GMT

Ooops!

You are right. I had checked again and there will be circular reference. I just wanted to understand the difference between Annee & Annee2 tables? Do they need to be seperate? or can we merge them as one single table?

Thanks.

two linked fields

Thu, 28 Apr 2011 07:58:50 GMT

we can merge them but it makes a circular reference.... I duplicated the hierarchy to avoid the circular reference but they are axactly same..

two linked fields

montubhardwaj — Thu, 28 Apr 2011 10:11:20 GMT

I looked into qvw file which you have attached but was very confusing. I am just giving you suggestion based on my understanding:

Seems like you want to join "anee2" with "anee". For that, first join Fact tables "anee" and "semestre" like this:

load * from semestre
left join
load * from annee

output of this will be a single table and say it "anee1" and it will have the below fields:
id_annee,
id_parcours,
intitule_parc,
id_periode,
valeur_annee

Now rename id_annee2 from "annee2" to id_annee

once you are done with this, I think you will be all set.
Hope that was helpful.