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.
To start selecting data, do the following:
- Click ± on a Folder data connector in the data load editor.
- Select file from the drop-down list of available files in the folder and click Select.
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:
- Check the box next to the table name.
If you want to select specific fields from a table, do the following:
- Click the table name (not in the checkbox). Fields is updated with the available table content, and you can select the fields you require.
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:
- Embedded field names, if field names (headers) are stored in the first line of data.
- No field names, if there are no field names.
- Set to the number of lines to ignore when loading data.
Do the following:
- Select the fields to include using the checkbox next to each field name.
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:
- If you load two files containing a field with the same name, they will by default be linked and treated as one field in Qlik Sense. If you want to load them as separate fields, rename the fields so that they are different.
- If you load two files containing a field that should be read as one field, but has different names in the respective files, you can rename them (in either file) to have identical names to load them as one field.
Do the following:
- Click on the field header you want to rename, type the new name and press Enter.
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:
- Click Insert script.
The Select data from window is closed, and the LOAD statements are inserted in the script in accordance with your selections.
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);