Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to specify a list of field names in a file (a list of 50 fields, for example). I would then like to read this file and use this list of field names to pull only this data from my data source. How can I do this within QlikView?
For example:
Suppose I have (1) a data mart with 100 fields (an excel workbook in this example), and (2) a config file (xls) which identifies exactly 5 columns.
My5Columns.xls:
[Cols]
col1
col2
col3
col4
col5
// Load the contents of the config file into memory --- maybe a cross-tab table?
My5Columns:
Load
Cols
From [..\My5Columns.xls]
(biff, embedded labels, table is [ColumnList]);
MyFinalDataResults:
Load
* // <------- This should not be an asterisk this should be the list of fields pulled from the file called "my5columns"
From [..\MyDataSource.xls]
(biff, embedded labels, table is [DataMart]);
Hi Thomas,
Please find the attached QVW file. I have taken some sample data and worked on your question. I am also attaching the sample data files for better understanding.
--> ExcelColumnsThomas.xlsx contains only the column names.
--> ExcelDataThomas.xlsx contains the data for the columns.
An elaborate explanation of what I have done in the QVW file. Please see below.
Explanation:-
1. I have used Concat script function to combine the column names in the first excel into one row delimiting by a comma.
2. Assigned that value to a variable using a Let function. Here for getting the field value, I have used FieldValue function, you can also use any other function like the Peek function.
3. Used this variable in the load statement of the second excel.
I hope you got your answer. Kindly let me know if you need further information.
Cheers,
Sarat.K
This is awesome saratchandra30, but just want to offer few upgrades
1) Add square brackets while concatenating because the field names might have spaces (check the update documents where I changed Col1 to Col 1)
Concat('[' & ColumnNames & ']' ,', ')
2) You don't need to concatenate on a resident load, you can save time by directly concatenating from the Excel load to run the script faster
Script
Columns:
LOAD Concat('[' & ColumnNames & ']' ,', ') as Temp
FROM ExcelColumnsThomas.xlsx
(ooxml, embedded labels, table is Sheet1);
LET val = FieldValue('Temp',1);
Final:
LOAD $(val)
FROM ExcelDataThomas.xlsx
(ooxml, embedded labels, table is Sheet1);
DROP Table Columns;
Thank you stalwar1 for the upgrades.