Vous travaillez chez un commissionnaire de transport. Vous recevez donc des commandes de transport de la part de vos clients et votre rôle est d'attribuer ces commandes à des transporteurs.
Un nouveau logiciel a été implanté dans la société pour suivre les commandes en temps réel et on souhaite donc pouvoir comparer les horaires de chargement et de livraison demandés par le client et les horaires réels obtenus grâce au nouveau logiciel. Ceci permettra d'évaluer les différents retards au niveau des usines (retard pour la préparation de la commande par exemple) mais également ceux qui concernent le transporteur (retard à la livraison par exemple).
Vous avez deux fichiers à disposition :
orders.csv
contient l'ensemble des commandes après affrètement, c'est-à-dire qu'il contient l'ensemble des commandes envoyées par le client ainsi que le transporteur que vous avez attribué à chaque commande.
Les champs sont les suivants :
OrderNumber : numéro de commande
LoadingPlace : ville de chargement
DeliveryPlace : ville de livraison
Carrier : le transporteur chargé d'effectuer le transport
LoadingDate : date de chargement demandée par le client
ETL (Estimated Time of Loading) : heure de chargement demandée par le client
DeliveryDate : date de livraison demandée par le client
ETA (Estimated Time of Arrival) : heure de livraison demandée par le client
realtime.csv
contient les commandes exploitées dans le logiciel de suivi. On y retrouve donc certaines commandes avec des horaires réels (pas toujours complets).
Les champs sont les suivants :
OrderNumber : numéro de commande
RealLoadingDate : date réelle de chargement
RealArrivalAtPlaceOfLoading : heure réelle de chargement
RealDeliveryDate : date réelle de livraison
RealArrivalAtPlaceOfDelivery : heure réelle de livraison
OrderNumber;LoadingPlace;DeliveryPlace;Carrier;LoadingDate;ETL;DeliveryDate;ETA
60055412;Thourotte;Dabrowa;Schade Polska;22-10-2014;10:00;25-10-2014;16:00
60087412;Torgau;Mannheim;Offergeld;10-05-2014;08:00;10-05-2014;18:00
60148965;Pise;Aniche;Lannutti;16-07-2014;06:00;18-07-2014;15:00
60058999;Low Eggborough;Aniche;Campeys of Selby;02-01-2014;16:00;03-01-2014;14:00
60016071;Salaise sur Sanne;Carentan;Transports Elero;03-03-2014;08:00;04-03-2014;10:00
60023562;Dabrowa;Jarozowiesc;ESA Poland;05-08-2014;06:00;06-08-2014;05:00
60056452;Aviles;Arbos;Carcaba;23-09-2014;12:00;23-09-2014;19:00
60023568;Aviles;Thourotte;Glasspartners;15-10-2014;15:00;17-10-2014;13:00
60098745;Aniche;Mannheim;Elbe Torgau Transporte;18-11-2014;09:00;18-11-2014;17:00
60032568;Carentan;Low Eggborough;Avitrans;10-02-2014;11:00;11-02-2014;10:00
60145963;Salaise sur Sanne;Szecin;Offergeld;16-03-2014;09:00;19-03-2014;18:00
60023231;Torgau;Dabrowa;Polonia Logistyka;20-06-2014;10:00;22-06-2014;12:00
60014569;Arbos;Lisbonne;Lannutti; 31-03-2014;06:00;01-04-2014;14:00
60023548;Thourotte;Pise;PKM Logistique;28-02-2014;16:00;02-03-2014;11:00
60025897;Mannheim;Low Eggborough;Campeys of Selby;25-07-2014;11:00;28-07-2014;09:00
OrderNumber;RealLoadingDate;RealArrivalAtPlaceOfLoading;RealDeliveryDate;RealArrivalAtPlaceOfDelivery
60055412;22-10-2014;10:45;25-10-2014;
60087412;10-05-2014;07:05;10-05-2014;18:30
60148965;;06:02;18-07-2014;14:00
60016071;04-03-2014;06:00;05-03-2014;11:25
60023562;05-08-2014;14:00;06-08-2014;11:30
60056452;;11:55;23-09-2014;19:05
60023568;15-10-2014;15:00;17-10-2014;16:00
60098745;18-11-2014;;18-11-2014;16:42
60032568;;;;
60145963;15-03-2014;19:50;19-03-2014;16:20
60023231;20-06-2014;09:00;;12:03
60014569;;;;
60023548;28-02-2014;16:00;02-03-2014;11:00
On veut procéder à la jointure des deux fichiers CSV. Pour cela, nous devons procéder en deux étapes car une jointure nécessite d'avoir trié les données au préalable.
Créez une nouvelle transformation :
Fichier > Nouveau > Transformation
La transformation, nommée par défaut Transformation 1
apparaît alors dans l'onglet Navigateur
Enregistrer
Sélectionner le répertoire dans lequel vous souhaitez enregistrer votre fichier et renommer la transformation si vous le souhaitez
Un fichier nomTransformation.ktr a été créé dans le répertoire que vous avez désigné.
Pour des transformations plus complexes que celle que nous allons réaliser, il est possible de changer le paramétrage en sélectionnant Paramètres
après un clic droit sur votre transformation.
Vous remarquez qu'il était également possible de renommer la transformation et d'indiquer le répertoire de sauvegarde directement dans cette fenêtre.
Ajoutez les deux fichiers sources.
Pour chaque fichier CSV, choisir dans la palette une Extraction depuis fichier CSV
.
Aller dans l'onglet Palette de création
.
Extraction
> Extraction depuis fichier CSV
.
Glisser-déposer deux de ces éléments vers la partie droite.
Configurer chaque élément.
A ce stade, tous les champs de votre fichier ont été récupérés et leur type a été automatiquement déduit de l'analyse qui a été faite du fichier par le logiciel.
En cliquant sur Prévisualiser, vous pouvez vérifier que toutes vos données sont importées correctement.
Faire un tri sur les deux fichiers (le tri est un préalable indispensable à la jointure).
Insérer deux étapes Transformation > Tri lignes
Relier chaque étape d'extraction avec une étape de tri
Sélectionner l'étape source et maintenir Shift
enfoncée tout en glissant vers l'étape destination
Choisir Sortie principale de l'étape
Configurer le tri pour qu'il se fasse sur le champ OrderNumber
On ordonne nos données en fonction du numéro de commande car c'est l'élément commun de nos deux sources de données et celui sur lequel nous baserons notre jointure.
Faire la jointure.
Insérer une étape Jointure lignes > Jointure comparaison de lignes
Relier les deux étapes de tri à cette étape de jointure
Configurer une jointure externe gauche sur le champ OrderNumber
Double-cliquer sur l'élément
Dans Première étape
sélectionner le tri correspondant au fichier Orders
et sélectionner le second tri pour Seconde étape
Fixer Type Jointure
à Left Outer
Récupérer les champs clés pour les deux étapes
Ne conserver que le champ OrderNumber dans les deux cas
Cliquer sur OK
En cliquant droit sur votre étape de jointure, vous pouvez choisir de prévisualiser votre flux de sortie.
Vous remarquez ainsi qu'on a bien toutes les commandes avec les horaires estimés et réels. On retrouve également les commandes qui ne figuraient pas dans le fichier realtime.csv
(ex 60058999) avec des valeurs nulles dans les champs non renseignés, d'où le choix d'une jointure externe gauche, pour ne pas perdre les informations du fichier de référence orders.csv
.
Pour l'analyse future de nos données, l'entreprise a souhaité que les horaires manquants soient considérés comme une "vraie" information.
Cela veut dire qu'on ne veut pas de valeurs nulles dans notre table mais plutôt une information du type "données inconnues" lorsqu'il manque une date ou un horaire.
Remplacez les valeurs nulles qui concernent les dates et les horaires réels avec une information de type String.
Mettre les champs de type Date au type String.
Insérer une étape Divers > Remplacer valeur nulle
Relier la jointure à cette étape
Configurer l'élément
Double-cliquer sur l'élément
Cocher Sélection par champs
Récupérer les champs
Ne conserver que les champs nommés Realxxxxxxxx dans la liste
Dans la colonne Remplacer par
mettre la chaîne de caractères que vous voulez faire apparaître
cliquer sur OK
Prévisualisez votre flux de sortie pour être sûr que les valeurs nulles ont bien été remplacées.
Supprimez la colonne OrderNumber_1
de votre flux de données.
Insérer une étape Transformation -> Altération structure de flux
Relier la dernière étape avec la nouvelle
Configurer l'élément pour obtenir le résultat demandé
Double-cliquer sur l'élément
Aller dans l'onglet Retirer
Récupérer champs
Retirer tous les champs de la liste sauf orderNumber_1
Cliquer sur OK
Exportez votre table dans un fichier Excel.
Insérer une étape Alimentation -> Alimentation fichier MS Excel
Relier cette étape avec la précédente
Indiquer l'emplacement et le nom du fichier à sauvegarder
Double-cliquer sur l'élément
Parcourir pour choisir le répertoire de sauvegarde et donner un nom à votre fichier
Dans l'onglet Champs, cliquer sur Récupérer champs
Cliquer sur OK
La modélisation de votre transformation est maintenant terminée et vous devriez avoir le résultat suivant :
Exécutez votre transformation.
Dans le menu principal Actions -> Exécuter
puis cliquer sur Démarrer
dans la fenêtre qui s'affiche.
Dans la partie basse de votre écran, vous trouvez les résultats d'exécution avec pour chaque étape un détail sur le nombre de lignes lues, écrites, etc...
Si vous voyez une ligne rouge apparaître dans la partie basse de votre espace de travail comme dans la figure ci-dessous, c'est qu'une erreur a été détectée sur l'étape surlignée.
Si vous n'avez pas d'erreur dans l'exécution de votre transformation, vous devriez avoir un fichier Excel contenant toutes vos commandes avec les différents horaires dans le répertoire que vous aviez choisi.
Transformation correctement configurée : exercice1.ktr