Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Add primary key into tables from import cell

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


3 Replies
MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand
Not applicable

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

Master III
Master III

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.

May you live in interesting times!