Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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]);
You already have a great answer to this question here: Add a key tables / Tabellen einen Schlüssel hinzufügen.
I'm sorry if this was seen as double post. My intension was to make the question available for english speaking people.
I'm currently very confused by this forum software. I see no clear structure where to find the things.
Regards
Albert
It's easy:
If you manage the English language well enough, you find an area that fits your topic and you post your topic there (and only there). If not, you (only) post it in the language specific area.
Anyway,
Since this topic is started twice and the answer is already in the other topic, I am locking this one.