Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi,
Is it the only key? Any synthetic key?
Ivan
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.
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...
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";
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...
Try to upload the document - even with sandbox data .... then it's a lot easier to help you ....