Add a key tables / Tabellen einen Schlüssel hinzufügen

[#1]

I work currently on the import of Excel files.

I have a folder with multiple Excel files.

Each file corresponds to a complete data set.

Each file contains multiple worksheets.

In the first worksheet to find general information about the file:

ID

product name

...

In the following worksheets are tables with further information. For example, the following columns:

Measured value, min, max, unit

Step control parameters, duration, time unit

...

I managed to read the data from each worksheet. However, the data are not linked.

My idea was to value 'ID' of the first worksheet to assign the tables when reading. Unfortunately, I find the manual and in the Forum only examples, in which all participating tables already have a key field. There is no way to edit the Excel spreadsheet.

Example:

SET filename = [\\ path \ to \ file \ * Computers * 3.xlsx];

LOAD

    [Name of the device] as name,

     Approval number as ID

FROM $ (filename)

(OOXML, embedded labels, table is General,

    filters (

        Remove (Row, RowCnd (CellValue, 2, StrCnd (null)))

        Remove (Row, RowCnd (CellValue, 3, StrCnd (null)))

        Transpose ()

        Remove (Row, RowCnd (CellValue, 1, StrCnd (null)))

        Remove (Row, RowCnd (CellValue, 2, StrCnd (null)))

    )

);

Protocol:

LOAD

     Art as [protokoll_art]

     Min as [protokoll_min]

     Max as [protokoll_max]

     Unit as [protokoll_einheit]

FROM $ (filename)

(OOXML, embedded labels, header is 4 lines, table is [log 1v3]);

IS:

LOAD

     Art as [ist_art]

     Min as [ist_min]

     Max as [ist_max]

     Unit as [ist_einheit]

FROM $ (filename)

(OOXML, embedded labels, header is 4 lines, table is [log 2v3]);

SHOULD:

LOAD

     Art as [soll_art]

     Min as [soll_min]

     Max as [soll_max]

     Unit as [soll_einheit]

FROM $ (filename)

(OOXML, embedded labels, header is 4 lines, table is [log 3v3]);

Suppliers:

LOAD

     Name as [lieferant_name]

     Road as [lieferant_strasse]

     HNr as [lieferant_hnr]

     Zip as [lieferant_PLZ]

     Place as [lieferant_ort]

FROM $ (filename)

(OOXML, embedded labels, header is 4 lines, table is [suppliers]);

- - - -

Ich bearbeite aktuell den Import von Excel-Dateien.

Ich habe einen Ordner mit mehreren Excel-Dateien.

Jede Datei entspricht einem vollständigen Datensatz.

Jede Datei enthält mehrere Arbeitsblätter.

Im ersten Arbeitsblatt finden sich allgemeine Informationen zur Datei:

• ID
• Produktname
• ...

In den darauf folgenden Arbeitsblättern befinden sich Tabellen mit weiteren Informationen. Z. B. folgende Spalten:

• Messwert, Min, Max, Einheit
• Schritt, Kontrollparameter, Dauer, Zeiteinheit
• ...

Mir ist es gelungen die Daten aus jedem Arbeitsblatt auszulesen. Allerdings sind die Daten nicht miteinander verknüpft.

Meine Idee war es den Wert 'ID' aus dem ersten Arbeitsblatt beim Einlesen den Tabellen zuzuweisen. Leider finde ich im Handbuch und im Forum nur Beispiele, bei dem alle beteiligten Tabellen bereits ein Schlüsselfeld besitzen. Es gibt keine Möglichkeit die Excel-Tabelle zu bearbeiten.

Beispiel:

SET filename = [\\path\to\file\*Geraete*3.xlsx];

LOAD

    [Bezeichnung des Gerätes]    as Bezeichnung,

     Zulassungsnummer                    as ID

FROM $(filename)

(ooxml, embedded labels, table is Allgemein,

    filters(

        Remove(Row, RowCnd(CellValue, 2, StrCnd(null))),

        Remove(Row, RowCnd(CellValue, 3, StrCnd(null))),

        Transpose(),

        Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),

        Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

    )

);

Protokoll:

LOAD

     Art         as [protokoll_art],

     Min         as [protokoll_min],

     Max         as [protokoll_max],

     Einheit     as [protokoll_einheit]

FROM $(filename)

(ooxml, embedded labels, header is 4 lines, table is [Protokoll 1v3]);

IST:

LOAD

     Art         as [ist_art],

     Min         as [ist_min],

     Max         as [ist_max],

     Einheit     as [ist_einheit]

FROM $(filename)

(ooxml, embedded labels, header is 4 lines, table is [Protokoll 2v3]);

SOLL:

LOAD

     Art         as [soll_art],

     Min         as [soll_min],

     Max         as [soll_max],

     Einheit     as [soll_einheit]

FROM $(filename)

(ooxml, embedded labels, header is 4 lines, table is [Protokoll 3v3]);

Lieferanten:

LOAD

     Name        as [lieferant_name],

     Straße      as [lieferant_strasse],

     HNr         as [lieferant_hnr],

     PLZ         as [lieferant_PLZ],

     Ort         as [lieferant_ort]

FROM $(filename)

(ooxml, embedded labels, header is 4 lines, table is [Lieferanten]);

Message was edited by Community Moderator to include English translation as a courtesy.

[marcus_sommer · Accepted solution]

Hallo Albert,

um alle Loads miteinander zu koppeln, könntest Du mit filename/filebasename() as FileKey einen eindeutigen Key schaffen. Neben dem würde ich die ID und die Bezeichnung in eine Variable schreiben und so, den anderen Loads hinzufügen und sehr vermutlich auch die weiteren Tabellen aneinander hängen, wie in diesem Beispiel hier:

SET filename = [\\path\to\file\*Geraete*3.xlsx];

ID:

First 1 LOAD

    [Bezeichnung des Gerätes]    as Bezeichnung,

     Zulassungsnummer                    as ID

FROM $(filename)

(ooxml, embedded labels, table is Allgemein,

    filters(

        Remove(Row, RowCnd(CellValue, 2, StrCnd(null))),

        Remove(Row, RowCnd(CellValue, 3, StrCnd(null))),

        Transpose(),

        Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),

        Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

    )

);

let vID = peek('ID', 0, 'ID');

let vBez = peek('Bezeichnung', 0, 'ID');

drop table ID;

Daten:

//Protokoll:

LOAD

     Art         as [protokoll_art],

     Min         as [protokoll_min],

     Max         as [protokoll_max],

     Einheit     as [protokoll_einheit],

     '$(vID)'    as ID,

     '$(vBez)'   as Bezeichnung,

     'Protokoll' as Typ,

     filename()  as FileName

FROM $(filename)

(ooxml, embedded labels, header is 4 lines, table is [Protokoll 1v3]);

concatenate(Daten)

//IST:

LOAD

     Art         as [ist_art],

     Min         as [ist_min],

     Max         as [ist_max],

     Einheit     as [ist_einheit],

     '$(vID)'    as ID,

     '$(vBez)'   as Bezeichnung,

     'Protokoll' as Typ,

     filename()  as FileName

FROM $(filename)

(ooxml, embedded labels, header is 4 lines, table is [Protokoll 2v3]);

concatenate(Daten)

//SOLL:

LOAD

     Art         as [soll_art],

     Min         as [soll_min],

     Max         as [soll_max],

     Einheit     as [soll_einheit],

     '$(vID)'    as ID,

     '$(vBez)'   as Bezeichnung,

     'Protokoll' as Typ,

     filename()  as FileName

FROM $(filename)

(ooxml, embedded labels, header is 4 lines, table is [Protokoll 3v3]);

Lieferanten:

LOAD

     Name        as [lieferant_name],

     Straße      as [lieferant_strasse],

     HNr         as [lieferant_hnr],

     PLZ         as [lieferant_PLZ],

     Ort         as [lieferant_ort],

     '$(vID)'    as ID,

     '$(vBez)'   as Bezeichnung,

     //'Protokoll' as Typ,

     filename()  as FileName

FROM $(filename)

(ooxml, embedded labels, header is 4 lines, table is [Lieferanten]);

Ich denke mal, dass bringt Dich auf weitere Ideen. Mehr zum Thema Datenmodellierung kannst Du hier finden: Get started with developing qlik datamodels

Schöne Grüße

Marcus

[#3]

Danke, hat sehr geholfen.

Hatte es hilfsweise über den Dateinamen gelöst:

SET dirpath = ;

SET filescheme = [*geraete*Anl*3];

sub workInDirectory (Root,Extlist)

    for each Ext in 'xlsx','xls'

        for each File in filelist (Root&'\$(filescheme).'&Ext)

            if FindOneOf('$(File)', '~$') = 0 then //Protects opening open documents.

               Filelist:

               LOAD

                    '$(File)'           as [FilePath],

                    FileSize('$(File)') as [FileSize],

                    FileTime('$(File)') as [FileTime]

                    autogenerate 1;

                              Allgemein:

               LOAD

                    '$(File)'                    as [FilePath],

                    [Bezeichnung des Gerätes]    as Bezeichnung,

                    Zulassungsnummer             as ID

               FROM $(filename)

               (ooxml, embedded labels, table is Allgemein,

                    filters(

                         Remove(Row, RowCnd(CellValue, 2, StrCnd(null))),

                         Remove(Row, RowCnd(CellValue, 3, StrCnd(null))),

                         Transpose(),

                         Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),

                         Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

                    )

               );

               Protokoll:

               LOAD

                   '$(File)'  as [FilePath],

                   Art        as [protokoll_art],

                   Min        as [protokoll_min],

                   Max        as [protokoll_max],

                   Einheit    as [protokoll_einheit]

               FROM $(filename)

               (ooxml, embedded labels, header is 4 lines, table is [Protokoll 1v3]);

                              ...

            end if

        next File

    next Ext

    for each Dir in dirlist (Root&'\*')

         call workInDirectory(Dir)

    next Dir

end sub

call workInDirectory('$(dirpath)','$(fileext)')