Hi there, assistance is required! (Fields are Dutch names)
The load from below is from a data-warehouse QVD and is the leading data that I have to join on.
dim_Wagenpark:
LOAD distinct
WagenparkID,
"Auto volgnummer",
Kenteken,
Capitalize(Merk) as Merk,
Capitalize(Model) as Model,
// Uitvoering,
Capitalize("Volledige auto omschrijving") as "Auto Omschrijving",
// "Kleur auto",
// "Kleur bekleding",
// Vervoermiddel,
// "Vervoermiddel categorie",
// Gewicht,
// "CO2 uitstoot",
"Kenteken soort (grijs/geel)",
// Inhuur,
// Chassisnummer,
"Datum - Inzetdatum",
// "Datum - Besteldatum",
"Datum kenteken - deel één" ,
// "Datum kenteken - deel drie",
"Datum - Verkoopdatum",
brandstofsoort as Brandstof,
"Brandstof etiket",
"Soort Lease",
Cataloguswaarde,
BPM,
"Catalogusprijs ex BPM/BTW",
"Boekwaarde inzet contract",
"Restwaarde einde contract (incl. BPM/Excl. BTW)"
FROM [lib://QVD Files - 'xx'/1op1 ml_sterschema/ml_dim_Wagenpark.qvd]
(qvd)
Now I have another excel document that I want to join on this data.
However, the excel document has way less data than the load above.
I need to add the field "Werkelijke kosten" to the data above while the key to join on is"Kenteken".
dim_component_446:
LOAD
Kenteken,
"Werkelijke kosten"
FROM [lib://AttachedFiles/Component 446.xlsx]
(ooxml, embedded labels, table is Blad1);
Basically I want field "Werkelijke kosten" added to the first table but since the second table has way less values it will return missing values, because the majority of "Kenteken" can not be linked.
I would like to know if it is possible to assign a 0 for "Werkelijke kosten" if the "Kenteken" can not be matched.
I think that is possible with applymap, but I do not really know how to advance.
Thank you very much!
