topic Re: Loading file which exists in a file list, loop? in QlikView

Loading file which exists in a file list, loop?

Fri, 15 Jan 2016 15:08:46 GMT

Hi,

I'm looking for a way to only load files where they exist in a table similar to the below where the ACTIVE flag is set to 1.

AREA	ACTIVE	FILENAME
North	1	201510_North_Party.txt
South	1	201510_South_Party.txt
East	0	201510_East_Party.txt
West	1	201510_West_Party.txt

Is there a way to have one load statement that loops through the table and loads the 3 files?

thanks

Stuart

Re: Loading file which exists in a file list, loop?

sunny_talwar — Fri, 15 Jan 2016 15:11:23 GMT

I guess create a concatenated list of FILENAME to be loaded in and loop through those.

Re: Loading file which exists in a file list, loop?

Fri, 15 Jan 2016 15:13:16 GMT

yes - I am unsure how to get the load statement to loop on a list of filenames?

Re: Loading file which exists in a file list, loop?

sunny_talwar — Fri, 15 Jan 2016 15:14:03 GMT

I am working on a sample for you to look at

Re: Loading file which exists in a file list, loop?

Fri, 15 Jan 2016 15:16:33 GMT

awesome

Re: Loading file which exists in a file list, loop?

Fri, 15 Jan 2016 15:18:34 GMT

Try like below:

FilesToBeLoad:

Load Distinct

FILE_NAME

From

source_meta_data

Where

ACTIVE =1

;

For i=1 to FieldValuecount('FILE_NAME')

Let vFileName = FieldValue('FILE_NAME',$(i))

FinalTable:

Load

From [$(vFileName)] (txt)

Next i

Re: Loading file which exists in a file list, loop?

sunny_talwar — Fri, 15 Jan 2016 15:19:21 GMT

Check this out:

Table:

LOAD Concat(FILENAME, '|') as List,

Count(FILENAME) as Count

FROM

[https://community.qlik.com/thread/200275]

(html, codepage is 1252, embedded labels, table is @1)

Where ACTIVE = 1;

LET vList = Chr(39) & Peek('List') & Chr(39);

LET vCount = Peek('Count');

DROP Table Table;

FOR i = 1 to $(vCount)

LET vFile = SubField($(vList), '|', $(i));

Table:

LOAD Dim,

Value

FROM

$(vFile)

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

NEXT i

Re: Loading file which exists in a file list, loop?

sunny_talwar — Fri, 15 Jan 2016 15:22:37 GMT

dathu.qv‌ what happens if there is duplicates in FILE_NAME field? Does it take the distinct count and distinct FieldValue? If it doesn't take distinct by default, is there a way to make it distinct? May be like FieldValueCount(DISTINCT 'FILE_NAME') or something along these lines??

Re: Loading file which exists in a file list, loop?

sunny_talwar — Fri, 15 Jan 2016 15:30:59 GMT

Re: Loading file which exists in a file list, loop?

Fri, 15 Jan 2016 15:32:06 GMT

thank you so much for the help -works excellently

Re: Loading file which exists in a file list, loop?

sunny_talwar — Fri, 15 Jan 2016 15:35:50 GMT

It does seem to take distinct count. I went to check the QlikView Help: QlikView ‒ FieldValueCount - script and chart function

Re: Loading file which exists in a file list, loop?

sunny_talwar — Fri, 15 Jan 2016 15:44:18 GMT

Not sure if it is more efficient or not, but I do like dathu's way of doing it as well (thanks for introducing a new method to me, as I was having aggregation issue on server and I am going to test if I can use your method as a workaround)

Table:

LOAD FILENAME

FROM

[https://community.qlik.com/thread/200275]

(html, codepage is 1252, embedded labels, table is @1)

Where ACTIVE = 1;

FOR i = 1 to FieldValueCount('FILENAME')

LET vFile = FieldValue('FILENAME', $(i));

Table:

LOAD Dim,

Value

FROM

$(vFile)

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

NEXT i

Re: Loading file which exists in a file list, loop?

Fri, 15 Jan 2016 15:58:44 GMT

FieldValueCount and FieldValue functions always return distinct values only. So no worry about duplicates while using FieldValueCOunt & FieldValue functions.

Re: Loading file which exists in a file list, loop?

sunny_talwar — Fri, 15 Jan 2016 16:00:00 GMT

Yup I had a similar finding. Just need to test if it performs better over Count and Concat aggregations