Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
i'm new to Qlik Sense Desktop and i have a question:
Is it possible to load some certain data out of an Excel-File?
For example i have the following table in my Excel-File:
Test | Test_1 | Test_2 | Data_1 | Data_2 | Country |
---|---|---|---|---|---|
12 | 13 | 15 | 1234 | 4321 | Ger |
34 | 56 | 78 | 9111 | 2342 | US |
7382 | 635 | 88761 | 66214 | 332185 | Frau |
I want to load all the fields that contain "Test" and "Data". So if there would be some more fields with these words i want to load them too.
Is that possible in Qlik Sense Desktop?
Greetings
Selecting data from a Microsoft Excel file
You can load from data from a selected Microsoft Excel file. When you have selected fields, you can insert the script code required to load the fields into the script.
See: How to prepare Excel files for loading with Qlik Sense
To start selecting data, do the following:
Select data from is displayed and the list of Tables is populated with the sheets and named areas available in the selected Microsoft Excel file.You can also use a Microsoft Excel file as data source using the ODBC interface. In that case you need to use an ODBC data connection instead of a Folder data connection.
The Tables list includes all sheets and named areas of the selected Microsoft Excel file.If you want to select all the fields in a table, do the following:
If you want to select specific fields from a table, do the following:
Tables that have all columns selected are indicated with a checkmark in the checkbox, while tables with some fields selected are indicated with a square in the checkbox and the number of selected fields to the right of the table name.
Fields lists all fields available in the selected table. You may need to specify the following settings:Field names:
Header size:
Do the following:
When you have selected the fields to include from the selected table, you can continue to select fields from other tables in the same file.
You can rename fields. This is particularly useful in the following two cases:
Do the following:
The field is renamed, and the script preview is updated if the field is selected.Renaming a field corresponds to using as in a field definition in a LOAD statement.
The statements that will be inserted are displayed in the script preview , which you can choose to hide by clicking Preview script.
When you have finished selecting fields and want to generate your LOAD/SELECT statements in the script, do the following:
The Select data from window is closed, and the LOAD statements are inserted in the script in accordance with your selections.
Hi Mchael,
but you have 6 Fields, do you mean the Field : Test and Data_1, give a bit more information of your expected output?
Hi Michael,
In the attached QVF file, the solution variant.
Regards,
Andrey
cool way of proceeding
Love it!
Hi,
I think the most straightforward way of achieving what you need is by transposing the table first. Try this:
FIELDS:
LOAD
CONCAT(IF(WILDMATCH(@1, '*Test*', '*Data*'), @1), ',') AS FIELD
FROM [lib://MyConnection/MyFile.xlsx] (ooxml, no labels, table is MySheet, filters(transpose()))
GROUP BY 1;
LET vFIELDS = PEEK('FIELD', 0, 'FIELDS');
DROP TABLE FIELDS;
TABLE:
LOAD
$(vFIELDS)
FROM [lib://MyConnection/MyFile.xlsx] (ooxml, embedded labels, table is MySheet);
Regards,
Diego.-