topic Re: Only load fields specified in a file in QlikView

Only load fields specified in a file

tdegen_qlik — Fri, 10 Aug 2018 02:27:04 GMT

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

Re: Only load fields specified in a file

Anonymous — Sat, 11 Aug 2018 09:31:26 GMT

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

Re: Only load fields specified in a file

sunny_talwar — Sat, 11 Aug 2018 11:20:01 GMT

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;

Re: Only load fields specified in a file

Anonymous — Sat, 11 Aug 2018 11:31:05 GMT

Thank you stalwar1‌ for the upgrades.