Add primary key into tables from import cell

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 you find general information about the file:

ID
product name
...

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]);

Re: Add primary key into tables from import cell

Gysbert_Wassenaar — Wed, 13 Apr 2016 16:38:03 GMT

You already have a great answer to this question here: Add a key tables / Tabellen einen Schlüssel hinzufügen.

Re: Add primary key into tables from import cell

Thu, 14 Apr 2016 08:26:25 GMT

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

Re: Add primary key into tables from import cell

oknotsen — Thu, 14 Apr 2016 11:04:26 GMT

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.

topic Re: Add primary key into tables from import cell in QlikView

Add primary key into tables from import cell

Re: Add primary key into tables from import cell

Re: Add primary key into tables from import cell

Re: Add primary key into tables from import cell