topic Re: Synthetic Key Issue in QlikView

Synthetic Key Issue

Mon, 26 Sep 2011 09:05:51 GMT

Hello All,

I am using Qlikview9.

In my qlikview model I have three tables A, B, C.

In A: invoiceno, week

In B: invoiceno, week

In C: week

If I will join these three tables it is giving me a synthetic ..

Please suggest how to avoid it...

Thanks.

Re: Synthetic Key Issue

Mon, 26 Sep 2011 09:22:42 GMT

Hi Sandeepa,

Do this!

TableA:
Load
     Invoiceno &'_'& week as InvoiceWeek, //Common Key for TableA and TableB
     invoiceno as TableA_Invoice, // Renamed to Avoid SyntheticKey
     week as Week //If you want to connect to TableC-> Week field
From TableA;
TableB:
Load
     Invoiceno &'_'& week as InvoiceWeek, //Common Key for TableA and TableB
     invoiceno as TableB_Invoice, // Renamed to Avoid SyntheticKey
     week as TableB_Week // If this is changed to Week, it will connect to Week in TableC
From TableB;
TableC:
Load
     Week // This can be connected to Week in TableA or TableB as wished
From TableC;

Hope that helps

Sravan

Synthetic Key Issue

SunilChauhan — Mon, 26 Sep 2011 09:30:12 GMT

try the following

tab1:

load

invoiceno,

week as wek_1

from path

left join(tab1)

Load

invoiceno, week

from path

left join(tab1)

load

week

from path

Re: Synthetic Key Issue

its_anandrjs — Mon, 26 Sep 2011 09:35:47 GMT

Hi,

I think u have to load

Tab1:

Load

invoiceno,

week as Tab1_Wk

From Table1

Tab2:

Join(Tab1)

Load

invoiceno,

week as Tab2_Wk

From Tabl2

Tab3:

Join(Tab1)

Load

week

From Table3

Let me know about this

Rgds

Anand

Re: Synthetic Key Issue

its_anandrjs — Mon, 26 Sep 2011 10:01:33 GMT

Hi,

I think your first two tables are already has same column name so i suggest you join them and third table is connect with your week field so that you get data of that table also.

Tab1:

Load * inline

[

invoiceno, week

in1,wk1

in2,wk1

];

Tab2:

Join(Tab1)

load * inline

[

invoiceno, week

in2,wk2

in3,wk2

];

Tab3:

load * inline

[

week

wk3

];

See attachement

Rgds

Anand

Re: Synthetic Key Issue

Mon, 26 Sep 2011 10:26:18 GMT

Please find attached of the qvw file.

Thanks.

Re: Synthetic Key Issue

Mon, 26 Sep 2011 10:30:34 GMT

Actually TableA and TableB should join by invoice no.

And TablceC should join TableA and TableB by week as I have to reference arrange the data by tableC.Week field.

Re: Synthetic Key Issue

SunilChauhan — Mon, 26 Sep 2011 10:34:22 GMT

take the attached file and reload

hope this will help you

thankss

Sunil

Re: Synthetic Key Issue

its_anandrjs — Mon, 26 Sep 2011 10:49:09 GMT

Hi,

See the attache file

HTH

Rgds

Anand

Synthetic Key Issue

SunilChauhan — Mon, 26 Sep 2011 11:54:28 GMT

anand,

but it still showing synthetic key.

if u see there is used two join with same table.

sandeepa

did you get the proper result.

Synthetic Key Issue

its_anandrjs — Mon, 26 Sep 2011 12:00:40 GMT

Hi,

Thanks, I am not able to reload your file but there is two join for two tables see the codes once

Join(TableA)

and

Join(TableB)

It works correctly

Rgds

Anand

Re: Synthetic Key Issue

Mon, 26 Sep 2011 12:11:44 GMT

Yeah. Synthetic key is still showing.. But I managed to get the output. As it is a kinda circular join can't avoid synthetic key...

Re: Synthetic Key Issue

its_anandrjs — Mon, 26 Sep 2011 12:18:17 GMT

Hi,

Thanks, Ok great rename that field or delete perticular field i suggest there is more than two fields that are connected to each other or also there is key between then unfortunately you have to delete atleast one field from table which makes circular reference.or you want to keep that field rename it or qualify that field with

Qualify Fieldname;

Rgds

Anand

Synthetic Key Issue

SunilChauhan — Mon, 26 Sep 2011 12:30:43 GMT

sounds good

keep it up both of you anand and sandeepa