Only load fields specified in a file

[tdegen_qlik]

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]);

[Anonymous]

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

[sunny_talwar]

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;

[Anonymous]

Thank you stalwar1‌ for the upgrades.