topic Re: Tabellen einen Schlüssel hinzufügen in QlikView

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

Wed, 25 Nov 2020 16:16:04 GMT

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:

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.

Re: Tabellen einen Schlüssel hinzufügen

marcus_sommer — Wed, 13 Apr 2016 07:19:39 GMT

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

Re: Tabellen einen Schlüssel hinzufügen

Fri, 15 Apr 2016 08:18:40 GMT

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)')