topic Re: loops in script in QlikView

loops in script

Thu, 21 Jan 2016 12:09:08 GMT

Hi All,

Today while referring to Henric post on Loops in the Script, I came to know that we can load data from excel file whose names are listed in a Tables using below script.

For vFileNo = 1 to NoOfRows('FileListTable')

Let vFileName = Peek('FileName',vFileNo-1,'FileListTable');

Load *,

'$(vFileName)' as FileName

From [$(vFileName)];

Next vFileNo

Now, In may case I want to load data from those excel files whose names are not listed in the table.

Where do I need to do the changes to achieve output as per my requirement.

Thanks in advance

Regards

Priyanka

Re: loops in script

marcus_sommer — Thu, 21 Jan 2016 12:31:26 GMT

Try:

For vFileNo = 1 to NoOfRows('FileListTable')

trace $(vFileNo);

Let vFileName = Peek('FileName',$(vFileNo)-1,'FileListTable');

trace '$(vFileName )'; // checking in log-file and process-window

Load *,

'$(vFileName)' as FileName

From [$(vFileName)] (ooxml, embedded labels, table is [YourTable]); // could be also biff-format

Next vFileNo

- Marcus

Re: loops in script

Fri, 22 Jan 2016 04:36:21 GMT

Hi Marcus,

Thanks for the prompt reply, But it is not working as accepted.

-Priyanka

Re: loops in script

jonathandienst — Fri, 22 Jan 2016 05:10:37 GMT

>>load data from those excel files whose names are not listed in the table

So how do you know which files to load? Perhaps you mean all the files in a folder except those listed? You could use something like this:

For Each vFile in FileList('e:\data\*.xlsx')

Let vFileName = SubField(vFile, '\', -1);

If Not(Exists(FileName, vFileName)) Then

...perform load

End If

Next //vFile

(adapt for the correct file path and name mask in FileList)

Re: loops in script

Thu, 28 Jan 2016 04:47:46 GMT

Hi Jonathan,

Thanks for your response.

For Each vFile in FileList('*.csv')
Let vFileName = SubField(vFile, '\', -1);
If Not(Exists(FileName, vFileName)) Then
Load *,
'$(vFile)' as FileName,
Date(Mid('$(vFileName)',Index('$(vFileName)','y')+2,Index('$(vFileName)','.')-4-Index('$(vFileName)','y')+2)) as Date
From [$(vFileName)];
End If
Next vFile

I have implemented above code but it is not working, while debugging I found that FileName value is Null.

Regards

Priyanka

Re: loops in script

PradeepReddy — Thu, 28 Jan 2016 06:20:31 GMT

try like this...

Let vFileName = SubField($(vFile), '\', -1);

Re: loops in script

Thu, 28 Jan 2016 06:27:31 GMT

Hi Pradeep,

It's not working.

I guess I am missing something in below line because FileName is storing Null value

If Not(Exists(FileName, vFileName)) Then

Regards

Priyanka

Re: loops in script

jonathandienst — Thu, 28 Jan 2016 06:36:49 GMT

The code assumed you had loaded the file names to exclude into a field called FileName as per your initial post. So FileName is a field, not a variable.

Re: loops in script

Thu, 28 Jan 2016 06:44:14 GMT

Hi,

Yes, I know that

Below is the complete script

ExcelNames:
Load distinct FileName, Now() as LoadDate
Resident Volume;

For Each vFile in FileList('*.csv')
Let vFileName = SubField(vFile, '\', -1);
If Not(Exists(FileName,vFileName)) Then
Load *,
'$(vFile)' as FileName,
Date(Mid('$(vFileName)',Index('$(vFileName)','y')+2,Index('$(vFileName)','.')-4-Index('$(vFileName)','y')+2)) as Date
From [$(vFileName)];
End If
Next vFile

ExcelNames is the table containing names of those files which I don't want to load again.

Re: loops in script

jonathandienst — Thu, 28 Jan 2016 06:56:10 GMT

And how many records are loaded into ExcelNames?

Re: loops in script

NareshGuntur — Thu, 28 Jan 2016 07:01:10 GMT

You create a mapping table with the list of files you don't want to load.

FilterFiles:

Mapping load * inline [

File,Flag

A1.txt, 0

A2,txt, 0

A3.txt, 0

];

And you load all the files with a where condition at the end.

Load....

.....

where applymap('FilterFiles',FileName,1)=1;

Re: loops in script

sasikanth — Thu, 28 Jan 2016 07:07:18 GMT

Hi,

try with a wildmatch

if not wildmatch(Filename,vFilename) THEN

Re: loops in script

tamilarasu — Thu, 28 Jan 2016 07:14:04 GMT

I guess, the file extension causing the problem, Try something like below.

ExcelNames:
Load distinct FileName, Now() as LoadDate
Resident Volume;

For Each vFile in FileList('*.csv')
Let vFileName = Replace(SubField(vFile, '\', -1),'.csv','');
If Not Exists(FileName,vFileName) Then
Load *,
'$(vFile)' as FileName,
Date(Mid('$(vFileName)',Index('$(vFileName)','y')+2,Index('$(vFileName)','.')-4-Index('$(vFileName)','y')+2)) as Date
From [$(vFileName)];
End If
Next vFile

Re: loops in script

settu_periasamy — Thu, 28 Jan 2016 07:50:44 GMT

try this (

ExcelNames:

Load distinct FileName, Now() as LoadDate

Resident Volume;

NoConcatenate

Concat_file:

LOAD

Concat(Chr(39)&FileName&chr(39),',') as FileList

Resident ExcelNames;

Let vFileList=Purgechar(Peek('FileList'),chr(39));

DROP Table Concat_file;

For Each vFile in FileList('*.csv')

Let vFileName = SubField(vFile, '\', -1);

If Not Match('$(vFileName)','$(vFileList)') then

Load *,

'$(vFile)' as FileName,

Date(Mid('$(vFileName)',Index('$(vFileName)','y')+2,Index('$(vFileName)','.')-4-Index('$(vFileName)','y')+2)) as Date

FROM

[$(vFileName)]

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

End If

Next vFile

Re: loops in script

Thu, 28 Jan 2016 08:23:57 GMT

I guess there is no issue with file extension as FileName column in ExcelNames Table contain extension of all files.

Re: loops in script

Thu, 28 Jan 2016 08:25:23 GMT

I tried this code, its is loading all the files. i.e filenames which are existing in the table as well as which are not.

Re: loops in script

settu_periasamy — Thu, 28 Jan 2016 08:30:49 GMT

It's worked for me. Did you try to check that in Debug mode?