Rotate an imported table

[rudywelvaert]

Hi,

I import an Excel-table with Data Manager. The table has 3 columns (KeyField, Column2, Column3) and 250 rows.

I want the Data Manager to rotate the table to the right so I get 250 columns and 3 rows.

In Qlik View it's done with script:

LOAD * FROM $(rootpath)$(xlsxpath)$(apppath)FileName.xlsx

(ooxml, embedded labels, table is TableName, filters(

Rotate(right)

))

WHERE not (Exists (KeyField));

But how can I do this in Qlik Sense?

R.W.

[Lisa_P]

This should also work in Qlik Sense

[rudywelvaert]

Thanks Lisa,

It doesn't work.

In the autogenerated secion I have this:

Set dataManagerTables = '','TableName';

//This block renames script tables from non generated section which conflict with the names of managed tables

For each name in $(dataManagerTables)

    Let index = 0;

    Let currentName = name;

    Let tableNumber = TableNumber(name);

    Let matches = 0;

    Do while not IsNull(tableNumber) or (index > 0 and matches > 0)

        index = index + 1;

        currentName = name & '-' & index;

        tableNumber = TableNumber(currentName)

        matches = Match('$(currentName)', $(dataManagerTables));

    Loop

    If index > 0 then

            Rename Table '$(name)' to '$(currentName)';

    EndIf;

Next;

Set dataManagerTables = ;

Unqualify *;

In the section to load the table:

[TableName]:

LOAD

[Key],

[English],

[Français]

FROM [lib://Connector/FileName.xlsx]

(ooxml, embedded labels, table is TableName, filters(

Rotate(right)

))

WHERE not (Exists (Key));

RENAME Field Key to LANGUAGE_SELECTOR;

The following error occurred:

Field 'English' not found

The error occurred here:

?

Data has not been loaded. Please correct the error and try loading again.

When I remove the Filter Rotate(right) and the WHERE statement:

[TableName]:

LOAD

[Key],

[English],

[Français]

FROM [lib://Connector/FileName.xlsx]

(ooxml, embedded labels, table is TableName);

RENAME Field Key to LANGUAGE_SELECTOR;

The load works, but of course I don't have the rotated table but the original table as in the Excel file

Started loading data

TableName<< TableName Lines fetched: 67

Creating search index Search index creation completed successfully

App saved

Finished successfully

0 forced error(s)

0 synthetic key(s)

R.W.

[petter]

Why don't you try:

[TableName]:

LOAD

*

FROM [lib://Connector/FileName.xlsx]

(ooxml, embedded labels, table is TableName, filters(

Rotate(right)

))

WHERE not (Exists (Key));

RENAME Field Key to LANGUAGE_SELECTOR;

[petter]

Normally you should pose a question by using the "Ask Question" not by creating a discussion. Nobody gets any credits by discussing - whereas a question will add credit for people who try to help you ...

I know this can be a bit confusing - I only say this as a friendly advice....

[rudywelvaert]

Petter,

Thanks this works

R.W.

[petter]

You're welcome - glad to be of help

[petter]

I was wrong it doesn't matter actually....