Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
This should also work in Qlik Sense
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.
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;
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....
Petter,
Thanks this works
R.W.
You're welcome - glad to be of help
I was wrong it doesn't matter actually....