topic Trouble linking tables together in QlikView

Trouble linking tables together

Thu, 29 Oct 2009 17:07:10 GMT

Got a linking problem. Made a printscreen of it and the red fussy line is the thing I want. Renaming "Kostenplaats" to "kp1" does not work. When the scripts loads, it ends with: Cannot execure script! Does anyone know a solution?

Trouble linking tables together

Thu, 29 Oct 2009 17:22:58 GMT

First, to ensure the linking on the red line then either:

Rename kp1 to kostenplaats

Rename kostenplaats to kp1

This will ensure that QlikView forms the links that you want, HOWEVER, and this is a big HOWEVER

You dont want that!!!!

This will create a circular reference which will make results completely unreliable, the linking that already exists in your picture above tells me that you should be able to execute any analysis you want already, you shouldn't need any more linking because all tables are linked. Do you have any specific problems that are being caused as a result of the above model?

Regards,

Trouble linking tables together

Thu, 29 Oct 2009 17:46:55 GMT

Hi,

you also can create a key table, which places the Kostenplaats or kp1 field as keyfield. Also take the Afdeling_ID into this key-table.

Then based on the Kostenplaats/kp1 you can add the Contactgroepomschrijving (don't exactly know why u use a textfield as keyfield in this mather; use the ID instead) to the keytable too and link the other tables to the keyfield.

That way you avoid getting a circular reference.

Rey-man

Trouble linking tables together

Thu, 29 Oct 2009 17:51:19 GMT

Well the thing is that the table dagen$ is a crosstable trough Excel. This crosstable links the everything to ContractgroepOmschrijving in the other table. BUT the kp1 is not linked to anything.

CrossTable(ContractgroepOmschrijving, Budget)

LOAD

Kostenplaats as kp1,

[1VG wonen] as [1VG],

[2VG wonen] as [2VG],

[3VG wonen] as [3VG],

[4VG wonen] as [4VG],

[5VG wonen] as [5VG],

[6VG wonen] as [6VG],

[7VG wonen] as [7VG],

[4LG wonen] as [4LG],

[7LG wonen] as [7LG],

[6GGZ_C wonen] as [6GGZ C]

FROM

D:\Qlikview9\Testbestanden\begroting.xls

(biff, embedded labels, table is dagen$);

The kp1 field must be matched to kostenplaats. If I change the crosstable to CrossTable(Kostenplaats, Budget) then this is solved but then the other fields are not matched with the field in the other table:

Load Omschrijving as ContractgroepOmschrijving,

"Contractgroep_ID";

SQL SELECT *

FROM "DataBI_Versie4".dbo."PCD_Contractgroep";

See, so I have to link those fields.... Maybe another way to do this?

Trouble linking tables together

Thu, 29 Oct 2009 17:54:36 GMT

Rey-Man, How does a key table work? And can you help me with it?

Trouble linking tables together

Thu, 29 Oct 2009 18:08:01 GMT

Hi Richard,

basicly you take the table which should create the circular reference as start.

In your case it should be the PCD_afdelingen or the dagen$ table,. Those tables both contain multiple links to other tables.

You should starting with loading the 1th table:

for example:

load the PCD_afdelingen.

Then load a new table with both the keys:

Kostenplaats and Afdeling_Id

Then drop one of the linkfields. from the PCD_Afdelingen

load the next table

Then join the key's into the keytable and drop all the key''s but one.

Then you should have:

------------- -------------- ------------

| table 1 | ------------------------- | keytable | ----------------------- | table 2 |

------------- -------------- ------------

Hope this is a little clear to you.

Rey-man

Trouble linking tables together

Thu, 29 Oct 2009 18:35:50 GMT

Never worked with keytables before so I hope you can get me started with my code. This is my code so can you tell me what to do? (The code is wrong because of the circular reference)

CROSSTABLE (ContractgroepOmschrijving,Budget,1)

LOAD

Kostenplaats,

[1VG wonen] as [1VG],

[2VG wonen] as [2VG],

[3VG wonen] as [3VG],

[4VG wonen] as [4VG],

[5VG wonen] as [5VG],

[6VG wonen] as [6VG],

[7VG wonen] as [7VG],

[4LG wonen] as [4LG],

[7LG wonen] as [7LG],

[6GGZ_C wonen] as [6GGZ C]

FROM

D:\Qlikview9\Testbestanden\begroting.xls

(biff, embedded labels, table is dagen$);

ODBC CONNECT TO DataBI;

SQL SELECT "Contractgroep_ID",

Omschrijving as ContractgroepOmschrijving

FROM "DataBI_Versie4".dbo."PCD_Contractgroep";

LOAD

"Afd_Extra1",

"Afd_Extra2",

"Afd_Extra3",

"Afdeling_ID",

"Instelling_ID",

IF(Trim(KostenplaatsNummer)= '', '40000',KostenplaatsNummer) as Kostenplaats,

LISZNummer,

Naam,

OmschrijvingAfdeling,

Sector,

"Sector_ID",

SoortAfdeling,

"SoortAfdeling_ID";

sql select *

FROM "DataBI_Versie4".dbo."PCD_Afdelingen";

LOAD "Afdeling_ID",

"AZRIndicatie_Bovengrens",

"AZRIndicatie_Gemiddelde",

"AZRIndicatie_Ondergrens",

"Contract_Hoeveelheid",

"Contract_HoeveelheidMAX",

"Contract_HoeveelheidMIN",

"Contractgroep_ID",

"Datum_ID",

"Eenheid_ID",

"Financiering_ID",

"Functie_code",

ID,

"Kostenplaats_ID",

Leeftijd,

"Bewoner_ID",

"Mutatie_ID",

"MutatieKoppeling_Hoeveelheid",

"MutatieKoppeling_HoeveelheidMAX",

"MutatieKoppeling_HoeveelheidMIN",

"Personeel_ID",

"Product_ID",

Productie,

ProductieAfspraak,

Reistijd,

TA,

Tarief,

"TariefCode_ID",

TeltInContractgroep,

"TypeRegistratie_ID",

"MutatieType_ID"

WHERE("MutatieType_ID" <> '173');

SQL SELECT *

FROM "DataBI_Versie4".dbo."PCD_ExploitatieTotaal";

Trouble linking tables together

Thu, 29 Oct 2009 19:56:22 GMT

Rey-man: Thx for your input but I cannot get it working. Can you take a look at my posted code above?

Trouble linking tables together

Thu, 29 Oct 2009 21:05:23 GMT

Hi Richard,

the beginning is good:

Should be something like this. Of course I've done this blind, so there may be some error's in the script.

ContractOmschrijvingBudget:

CROSSTABLE (ContractgroepOmschrijving,Budget,1)

LOAD

Kostenplaats,

[1VG wonen] as [1VG],

[2VG wonen] as [2VG],

[3VG wonen] as [3VG],

[4VG wonen] as [4VG],

[5VG wonen] as [5VG],

[6VG wonen] as [6VG],

[7VG wonen] as [7VG],

[4LG wonen] as [4LG],

[7LG wonen] as [7LG],

[6GGZ_C wonen] as [6GGZ C]

FROM

D:\Qlikview9\Testbestanden\begroting.xls

(biff, embedded labels, table is dagen$);

//Drop ContractgroepOmschrijving or Kostenplaats FROM ContractOmschrijvingBudget.

//Even better is to create a combined key: ContractgroepOmschrijving & Kostenplaats AS %ContractOmschrijvingBudget_Key (in the crosstable and keytable, then drop both ContractgroepOmschrijving and Kostenplaats) //DROP FIELDS <fieldnames> FROM ContractOmschrijvingBudget;

KeyTable:

LOAD

ContractgroepOmschrijving

,Kostenplaats

RESIDENT ContractOmschrijvingBudget;

ODBC CONNECT TO DataBI;

SQL SELECT "Contractgroep_ID",

Omschrijving as ContractgroepOmschrijving

FROM "DataBI_Versie4".dbo."PCD_Contractgroep";

Afdeling:

LOAD

Afdeling_ID & Kostenplaats AS %Afdeling_Key;

LOAD

"Afd_Extra1",

"Afd_Extra2",

"Afd_Extra3",

"Afdeling_ID",

"Instelling_ID",

IF(Trim(KostenplaatsNummer)= '', '40000',KostenplaatsNummer) as Kostenplaats,

LISZNummer,

Naam,

OmschrijvingAfdeling,

Sector,

"Sector_ID",

SoortAfdeling,

"SoortAfdeling_ID"

;

sql select *

FROM "DataBI_Versie4".dbo."PCD_Afdelingen";

CONCATENATE (KeyTable)

LOAD

Kostenplaats,

%Kostenplaats_Key,

Afdeling_ID

RESIDENT Afdeling;

DROP FIELDS Afdeling_ID, %Kostenplaat_Key FROM Afdeling;

PCD_Contractgroep:

LOAD "Afdeling_ID",

"AZRIndicatie_Bovengrens",

"AZRIndicatie_Gemiddelde",

"AZRIndicatie_Ondergrens",

"Contract_Hoeveelheid",

"Contract_HoeveelheidMAX",

"Contract_HoeveelheidMIN",

"Contractgroep_ID",

"Datum_ID",

"Eenheid_ID",

"Financiering_ID",

"Functie_code",

ID,

"Kostenplaats_ID",

Leeftijd,

"Bewoner_ID",

"Mutatie_ID",

"MutatieKoppeling_Hoeveelheid",

"MutatieKoppeling_HoeveelheidMAX",

"MutatieKoppeling_HoeveelheidMIN",

"Personeel_ID",

"Product_ID",

Productie,

ProductieAfspraak,

Reistijd,

TA,

Tarief,

"TariefCode_ID",

TeltInContractgroep,

"TypeRegistratie_ID",

"MutatieType_ID",

Kostenplaats & Afdeling_ID & Contractgroep_ID AS %PCD_ExploitatieTotaal_Key

WHERE("MutatieType_ID" <> '173');

SQL SELECT *

FROM "DataBI_Versie4".dbo."PCD_ExploitatieTotaal";

CONCATENATE (KeyTable)

LOAD

%PCD_ExploitatieTotaal_Key

,Kostenplaats

,Afdeling_ID

,Contractgroep_ID

RESIDENT PCD_Contractgroep;

DROP FIELDS Kostenplaats, Afdeling_ID, Contractgroep_ID FROM PCD_Contractgroep;

Good luck

Rey-man

Trouble linking tables together

Thu, 29 Oct 2009 21:58:25 GMT

thx for your input. I'm gonna try to get this working.