Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Loading file which exists in a file list, loop?

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.

AREAACTIVEFILENAME

North

1201510_North_Party.txt
South1201510_South_Party.txt
East0201510_East_Party.txt
West1201510_West_Party.txt

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

thanks

Stuart

1 Solution

Accepted Solutions

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

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



13 Replies

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

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

Not applicable

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

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?

I am working on a sample for you to look at

Not applicable

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

awesome

Not applicable

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

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?

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?

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?

Capture.PNG

Not applicable

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

thank you so much for the help  -works excellently

Community Browser