DataCollector SELECT tips – BlueDolphin

Datumconversies

Bij het importeren van datumvelden met de DataCollector, kunt u problemen ondervinden met het datumformaat. Er zijn veel manieren om een datum weer te geven, bijvoorbeeld:

14-03-2023
14-3-2023
14 maart 2023
2023/03/14
03/14/2023

Stel dat u gegevens wilt importeren vanuit een Excel-sheet. Het formaat van datums, zoals aangeboden door de DataCollector aan BlueDolphin, hangt af van veel factoren: het daadwerkelijke formaat van de ruwe gegevens in Excel, de manier waarop datums worden weergegeven in Excel (celopmaak), uw taal- en regio-instellingen van Microsoft Office en de taal- en regio-instellingen van het besturingssysteem.

Typisch zal uw SELECT-opdracht in de DataCollector zijn:

SELECT * FROM [tabelnaam$]

Hier is "tabelnaam" de naam van het werkblad in uw Excel-werkmap. Door simpelweg 'alle' kolommen te selecteren met '*', moet de DataCollector het formaat van datumvelden 'raden'. Als datums niet in het juiste formaat worden geïmporteerd in BlueDolphin, blijven de vragenlijstvelden leeg. Het kan nogal lastig zijn om op deze manier datums te importeren.

Gelukkig is er een eenvoudige manier om dit te verhelpen, door alle velden (= kolomnamen van uw Excel) expliciet te vermelden in de SELECT-opdracht. Gebruik de Format()-functie om het juiste datumformaat voor datumvelden af te dwingen.

Bijvoorbeeld:

Het voorbeeld van de select-query is:

SELECT  [Title],
        [Application ID],
        [Application Owner],
        [Application Name],
        [Application Vendor],
        [Application Type],
        [Hosting Type],
        [More Info],
        [Application Description],
        Format([Contract Start Date], "dd-MM-yyyy") AS [Contract Start Date],
        Format([Contract End Date], "dd-MM-yyyy") AS [Contract End Date],
        Format([End-of-Life Date], "dd-MM-yyyy") AS [End-of-Life Date],
        Format([End-of-Service Date], "dd-MM-yyyy") AS [End-of-Service Date],
        [OPEX],
        [CAPEX],
        [Relative Cost Indication],
        [Connectivity],
        [Life Cycle Status]
FROM    [Applications$]

Alle datumwaarden voor Contract Start Date, Contract End Date, End-of-Life Date en End-of_Service Date, worden nu expliciet geconverteerd naar “dd-MM-yyyy” (bijvoorbeeld: 14-03-2023). BlueDolphin accepteert dit datumformaat en gegevensvelden worden correct ingevuld.

Filter gegevens voor import

Hoewel we aanbevelen om de gegevens in uw primaire databron zo schoon en correct mogelijk te hebben, kan het nuttig zijn om filters te definiëren om selectief gegevens uit te sluiten van de import. Dit kunt u doen door logische voorwaarden toe te voegen aan de WHERE-clausule van de SELECT-opdracht. Enkele voorbeelden:

Selecteer alleen actieve applicaties

SELECT  [Title],
        [Application ID],
        ...
        [Life Cycle Status]
FROM    [Applications$]
WHERE   [Life Cycle Status] = 'Active'

Laat records weg die geen applicatienaam hebben

SELECT  [Title],
        [Application ID],
        [Application Name],
        ...
FROM    [Applications$]
WHERE   [Application Name] IS NOT NULL

Selecteer alleen applicaties boven een bepaalde kostendrempel

SELECT  [Title],
        [Application ID],
        ...
        [OPEX],
        ...
FROM    [Applications$]
WHERE   [OPEX] >= 10000

Combineer dit alles met logische uitdrukkingen

SELECT  [Title],
        [Application ID],
        [Application Name],
        ...
        [OPEX],
        ...
        [Life Cycle Status]
FROM    [Applications$]
WHERE   [OPEX] >= 10000 AND
        [Life Cycle Status] = 'Active' AND
        [Application Name] IS NOT NULL

Gegevens manipuleren voor import

Op een vergelijkbare manier kunt u gegevens manipuleren voordat u ze importeert. Enkele voorbeelden:

Bereken jaarlijkse uitgaven op basis van maandelijkse licentiekosten

SELECT  [Title],
        [Application ID],
        ...
        [Monthly License Costs] * 12 AS [OPEX],
        ...
FROM    [Applications$]
WHERE   ...

De brondata (bijvoorbeeld een Excel-bestand) heeft een kolom genaamd 'Maandelijkse Licentiekosten'. Dit wordt vermenigvuldigd met 12 om de jaarlijkse operationele uitgaven te krijgen. De resulterende waarde wordt opgeslagen in een kolom genaamd 'OPEX'.

Tekstwaarden samenvoegen

SELECT  [Title],
        [Application ID],
        ...
        CONCAT([FirstName], ' ', [LastName]) AS [Application Owner]),
        ...
FROM    [Applications$]
WHERE   ...

Met de CONCAT()-functie worden voornaam en achternaam aan elkaar geplakt en opgeslagen in een kolom genaamd [Applicatie-eigenaar]. Let op de spatie tussen de twee waarden.

Verdere documentatie

Raadpleeg de documentatie van W3Schools voor verdere lectuur. Enkele suggesties:

SELECT-instructie: https://www.w3schools.com/sql/sql_where.asp
WHERE-clausule: https://www.w3schools.com/sql/sql_where.asp
Logische uitdrukkingen: https://www.w3schools.com/sql/sql_and_or.asp
Functies: https://www.w3schools.com/sql/sql_ref_sqlserver.asp
Datatypen: https://www.w3schools.com/sql/sql_dates.asp