topic Re: Rotate an imported table in App Development

Rotate an imported table

rudywelvaert — Wed, 18 Jul 2018 14:09:27 GMT

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.

Re: Rotate an imported table

Lisa_P — Thu, 19 Jul 2018 06:19:39 GMT

This should also work in Qlik Sense

Re: Rotate an imported table

rudywelvaert — Thu, 19 Jul 2018 08:57:15 GMT

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.

Re: Rotate an imported table

petter — Thu, 19 Jul 2018 11:41:52 GMT

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;

Re: Rotate an imported table

petter — Thu, 19 Jul 2018 11:50:18 GMT

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....

Re: Rotate an imported table

rudywelvaert — Thu, 19 Jul 2018 15:39:19 GMT

Petter,

Thanks this works

R.W.

Re: Rotate an imported table

petter — Thu, 19 Jul 2018 16:39:25 GMT

You're welcome - glad to be of help

Re: Rotate an imported table

petter — Thu, 19 Jul 2018 16:39:51 GMT

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