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 you find general information about the file:
But there is only one entry in this table - no more.E.g.: ID="1234", Product name="Booster", Status="normal"In the following worksheets are tables with further information. For example, the following columns:
- Measured value, min, max, unit
- Work item, control parameters, duration, time unit
- ...
I managed to read the data from each worksheet. However, the QlikView tables are not linked.
My idea was to write the ID value from the first worksheet in every row/data entry in the tables during the
data extraction. Unfortunately, I find in this forum and in the manual only examples for tables with existing key fields.
There is no way to edit the Excel spreadsheet.
How can I add one new field/column into a table and fill this with a value from an other field from an other table.
E.g. add the field 'ID' in the table 'Log' and use for every entry (during extraction) the value from the table 'General'.
Example:
SET filename = [\\path\to\file\*Computers*3.xlsx];
General:
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)))
)
);
Log:
LOAD
Measurement as [record_measurement]
Min as [record_min]
Max as [record_max]
Unit as [record_unit]
FROM $(filename)
(OOXML, embedded labels, header is 4 lines, table is [log 1v3]);
ActualValue:
LOAD
Measurement as [actual_measurement]
Min as [actual_min]
Max as [actual_max]
Unit as [actual_unit]
FROM $(filename)
(OOXML, embedded labels, header is 4 lines, table is [log 2v3]);
NominalValue:
LOAD
Measurement as [nominal_measurement]
Min as [nominal_min]
Max as [nominal_max]
Unit as [nominal_unit]
FROM $(filename)
(OOXML, embedded labels, header is 4 lines, table is [log 3v3]);
Suppliers:
LOAD
Name as [supplier_name]
Road as [supplier_road]
No as [supplier_no]
Zip as [supplier_zip]
City as [lieferant_city]
FROM $(filename)
(OOXML, embedded labels, header is 4 lines, table is [suppliers]);
