topic Re: Exclude files from load, depending on their header. in QlikView

Exclude files from load, depending on their header.

varmekontrol — Wed, 25 Nov 2020 16:16:04 GMT

I want to know if it is possible to exclude files depending on the headers in the file.

Say I have two different files with different headers in the same folder (in this case I have millions and more like 6 different filetypes)

File 1: Headers

SerialNo

Created

Volume1

Volume2

Volume3

Volume4

File 2: Headers

SerialNo

Created

Heat1

Heat2

Heat3

Heat4

Temperature

Delta-T

ErrorCode

I am looking for something like

load

SerialNo,
Created,
Volume1,
Volume2,
Volume3,
Volume4

from *.csv where header = Volume1 or Volume2

And then I would make another load

load 
SerialNo,	
Created,	
Heat1,	
Heat2,	
Heat3,	
Heat4,	
Temperature,	
Delta-T,
ErrorCode,

from *.csv where header = Heat1

I am trying to achieve something like another open question I have in the community, just instead of separating the content in a folder, depending on the headers. -> Here

Re: Exclude files from load, depending on their header.

mrybalko — Tue, 19 Mar 2019 14:37:59 GMT

@varmekontrol wrote:
Hi
I want to know if it is possible to exclude files depending on the headers in the file.

Yes it is possible. You can read first row and determine header types. But it is required to load every file in your huge storage.

Is your task conversion numerous csv to one qvd for every header type?

Re: Exclude files from load, depending on their header.

varmekontrol — Tue, 19 Mar 2019 14:47:26 GMT

Thank you for your answer @mrybalko

My task is to use qlik to filter out the different 6 types of files that are in the folder. The files are from an automated system that delivers them daily to a folder, and the system does not know the difference between the different files.

From that filtering, I need to filter out a table with all the files with that specific header. Then repeat for the last 5 types.

Re: Exclude files from load, depending on their header.

mrybalko — Wed, 20 Mar 2019 11:31:47 GMT

@varmekontrol

Find example in attachment. I assume that you have enough RAM to load all csv at once.

I suggest to create individual qvw for every csv type and split qvds if they exceed 200 MB

Re: Exclude files from load, depending on their header.

varmekontrol — Wed, 20 Mar 2019 12:09:03 GMT

@mrybalko

Thank you for the blueprint and description of how to solve this problem.

I have 20+ GB ddr4 ram, so should not be a problem as I think I will also limit the historical data by only a couple of week.,

Thank you for all the work, and it works like a charm 🙂

Re: Exclude files from load, depending on their header.

varmekontrol — Wed, 20 Mar 2019 13:28:19 GMT

@mrybalko

Is there any way to prevent the script from generating an error when running into an empty file?

There were no fields to load in the given source.
LOAD *
FROM [C:\Users\sorensda\Documents\QlikView\Alarm og Rapport system\Convert\Input\0006046949_60749040_valuereport_20190317033736_2105.csv]
(txt, utf8, embedded labels, delimiter is ';', msq)

Re: Exclude files from load, depending on their header.

mrybalko — Wed, 20 Mar 2019 14:34:15 GMT

You can check file size using filesize()

FOR Each vCurFile in FileList('Input\*.csv')
	
	IF FileSize('$(vCurFile)') > 0 THEN
		//QV uses by default autoconcatenate to tables with the same fields set	
		LOAD *
		FROM [$(vCurFile)]
		(txt, utf8, embedded labels, delimiter is ';', msq);
		
	ENDIF //IF FileSize('$(vCurFile)') > 0 THEN
	
NEXT //FOR Each vCurFile in FileList('Input\*.csv')