Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

inigoelv
Contributor

Automatic load files with additional static column

Hi everybody:

I need the help with one script.

I need load several excell files, more than 100, with the same structure.

For this I have used the following script which works very weel:

Set vConcatenate = ;

sub ScanFolder(Root)

          for each FileExtension in 'xls'

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

                              FileList:

                              $(vConcatenate)

                              LOAD

                              *,

                              '$(FoundFile)' as SourceFile

                              FROM [$(FoundFile)] (biff, embedded labels, header is 3 lines, table is Hoja1$) where [NUM. ROLLO]<>'0';

                       

                              Set vConcatenate = Concatenate;

                    next FoundFile

          next FileExtension

          for each SubDirectory in dirlist( Root & '\*' )

                    call ScanFolder(SubDirectory)

          next SubDirectory

end sub

Call ScanFolder('C:\Users\inigoelv\Documents\Informes') ;

The problem is in all files  are additional information in some specific cell value, it is only one value.

When I try to insert that specific value as value common in every row of each file that doesn´t works:

left join

                              FIRST 1 LOAD

                              F3 as Descripcion,

                              F21 as Orden

                              FROM [$(FoundFile)] (biff, embedded labels, header is 1 lines, table is Hoja1$);

left join

                              FIRST 1 LOAD

                              @19 as MP

                              FROM

                              [$(FoundFile)]

                              (biff, no labels, header is 1 lines, table is Hoja1$); 

Depending of the position I inserted, some times freeze, some times works with the first file and no with the others, etc...

Any solution?

Thanks in advance.

2 Replies
MVP & Luminary
MVP & Luminary

Re: Automatic load files with additional static column

You should really fix this in your excel sheets. Putting that one specific value on every row in an extra column would be the easiest fix. You'd have to make no changes to the script.

An other solution is more complicated. You could create a new table with two fields: the file name of the excel as SourceFile and the specific single value in a Value field. Create that table first as a mapping table. Then when you load the data from the excel files in the loop you can use Applymap to add a column using the file name to look up the value in the mapping table.


talk is cheap, supply exceeds demand
satheshreddy
Contributor III

Re: Automatic load files with additional static column

Hi Elviro,

Same Extention? what ever u r loading files.

Regards

Sathish