topic keyfield problem in QlikView

keyfield problem

Fri, 13 Nov 2009 15:43:46 GMT

Got a problem and don't know what I'm doing wrong. Have a SQL db and an Excel file and want to match 2 rows. Everything seems correct (data is matched with the right costcenter), but at some costcenters there is missing some data from the Excel sheet.

The weird thing is that it does recognise the costcenters and puts data in certain departments but not all departements, and in other cases it puts data in all departements.

Can someone tell me what can be the problem? I match a crosstable (excel) with SQL on 2 rows like this:

Crosstable blablabla....

load departement & ' - ' & costcenter as %Keybudget (from the excel file)

.........

load dep & ' - ' & cos1 as %Keybudget (from the sql db)

The %Keybudget is the field I want to match and in some cases the data is not linked to the right costcenter.

keyfield problem

Fri, 13 Nov 2009 15:53:35 GMT

Could you try the following:

load autonumberhash128(upper(departement),upper(costcenter) as %Keybudget (from the excel file)

.........

load autonumberhash128(upper(dep),upper(cos1)) as %Keybudget (from the sql db)

If department is a number I would add NUM(dep) and num(departement).

In some cases you need to force the conversion so that key fields will match.

Rgds

keyfield problem

Fri, 13 Nov 2009 15:55:13 GMT

Hi,

Is it the only key? Any synthetic key?

Ivan

keyfield problem

Fri, 13 Nov 2009 17:12:34 GMT

Hi Spastor,

I've tried the autonumerhash128 but unfortunatly it does not change anything. The department is not a number but a like "2VG" (so part is a number). When i set it as a number the values don't match at all.

Maybe another idea?

@Ivan: Yes this is the only key and NO there are no synthetic keys.

keyfield problem

Fri, 13 Nov 2009 17:48:26 GMT

So in this case I would try that:

load autonumberhash128(upper(trim(departement)),upper(trim(costcenter)) as %Keybudget (from the excel file)

.........

load autonumberhash128(upper(trim(dep)),upper(trim(cos1))) as %Keybudget (from the sql db)

If it is not working for some code, that means that there is another difference than a space or a case... After reloading, the data, could you show us the data model picture ? Just to be sure...

keyfield problem

Fri, 13 Nov 2009 22:58:52 GMT

Still does not work. Dep, department, costcenter are not the real names but for the example I've changed them. I've removed the (autonumerhash128 string) My original full code=

The %KeyBudget field is the one I want to match

-----

BudgetTemp:

CrossTable(ContractgroepOmschrijving, Budget,1)

LOAD

Kostenplaats as kpl,

[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$);

Budget:

LOAD

ContractgroepOmschrijving & '-' & kpl as %KeyBudget,

Budget

RESIDENT BudgetTemp;

DROP Table BudgetTemp;

ODBC CONNECT TO DataBI;

Algemeen_DatumTabel:

LOAD

CAKPeriodeJaar,

CAKPeriodeJaarNummer,

CAKPeriodeNummer,

CAKPeriodeOmschrijving,

Dag,

DagVanDeWeek,

DagVanDeWeekOmschrijving,

Datum,

ID as "Datum_ID",

IsFeestdag,

IsWeekdag,

Jaar,

JaarKwartaal,

JaarMaand,

JaarWeek,

Kwartaal,

KwartaalOmschrijving,

LopendDagJaar,

LopendDagKwartaal,

Maand,

MaandJaarText,

MaandOmschrijving,

WeekJaar,

Weeknummer,

ZitInConceptPeriode,

ZitInProductiePeriode

WHERE(Jaar = '2009');

SQL SELECT *

FROM "DataBI_Versie4".dbo."Algemeen_DatumTabel";

Afdelingen:

LOAD

"Afd_Extra1",

"Afd_Extra2",

"Afd_Extra3",

"Afdeling_ID",

"Instelling_ID",

IF(Trim(KostenplaatsNummer)= ''and 0, '40000',KostenplaatsNummer) as KostenplaatsTemp,

LISZNummer,

Naam,

OmschrijvingAfdeling,

Sector,

"Sector_ID",

SoortAfdeling,

"SoortAfdeling_ID";

sql select *

FROM "DataBI_Versie4".dbo."PCD_Afdelingen";

ExploitatieTotaalTemp:

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' and "MutatieType_ID" <> '152');

sql select

FROM "DataBI_Versie4".dbo."PCD_ExploitatieTotaal";

JOIN (ExploitatieTotaalTemp)

LOAD

Omschrijving as ContractgroepOmschrijving,

"Contractgroep_ID";

SQL SELECT *

FROM "DataBI_Versie4".dbo."PCD_Contractgroep";

JOIN (ExploitatieTotaalTemp)

LOAD

"Afdeling_ID",

KostenplaatsTemp as Kostenplaats

RESIDENT Afdelingen;

DROP FIELD KostenplaatsTemp;

ExploitatieTotaal:

LOAD

ContractgroepOmschrijving & '-' & Kostenplaats as %KeyBudget

RESIDENT ExploitatieTotaalTemp;

DROP TABLE ExploitatieTotaalTemp;

Mutaties:

LOAD

"Afdeling_ID" as AfdelingsID,

BeginDatum,

"Bewoner_ID" as BewonersID,

EindDatum,

"Mutatie_ID",

"MutatieType_ID" as MutatieTypeID;

sql select *

FROM "DataBI_Versie4".dbo."PCD_Mutaties";

SQL SELECT *

FROM "DataBI_Versie4".dbo."PCD_Financiering";

SQL SELECT *

FROM "DataBI_Versie4".dbo."PCD_KS_ExploitatieTotaal";

SQL SELECT Beschrijving,

Eenheid,

"MutatieType_ID",

Omschrijving as OmschrijvingMutatie,

ProductieRealisatieIn,

"Proj_Extra1",

"Proj_Extra2",

"Proj_Extra3"

FROM "DataBI_Versie4".dbo."PCD_MutatieTypes";

SQL SELECT *

FROM "DataBI_Versie4".dbo."PCD_Clienten";

SQL SELECT Code,

"Extern_ID",

Omschrijving as OmschrijvingProduct,

"Prod_Extra1",

"Prod_Extra2",

"Prod_Extra3",

"Productcode_ID" as "Product_ID"

FROM "DataBI_Versie4".dbo."PCD_Producten";

SQL SELECT OmschrijvigType,

"TypeRegistratie_ID"

FROM "DataBI_Versie4".dbo."PCD_TypeRegistratie";

keyfield problem

Fri, 13 Nov 2009 23:00:12 GMT

My datamodel picture:

keyfield problem

Fri, 13 Nov 2009 23:02:03 GMT

I can't help you with the script... If Qlikview doesn't match all the data that means that they are not same on both side.

To test, just load the two tables in a test application an keep a field Key1 and a field Key2 then check both values when non matching...

keyfield problem

klausskalts — Fri, 12 Aug 2011 13:43:37 GMT

Try to upload the document - even with sandbox data .... then it's a lot easier to help you ....