topic loading multiple files with wildcard in QlikView

loading multiple files with wildcard

Wed, 29 Jun 2011 10:15:18 GMT

Hi!
Loading multiple transaction files using from c:/filename*.txt.
filename have a suffix depending on company within a large organization.

The problem occur when I want to ApplyMap, only the last filename gets mapped.
How to handle this issue?

rgds thomas

loading multiple files with wildcard

Wed, 29 Jun 2011 13:44:58 GMT

Hello Thomas,

didn't understand you exactly when you say:

The problem occur when I want to ApplyMap, only the last filename gets mapped.

Nevertheless: may be the use of a For-Each-Loop is what you need:

. . .

for each File in filelist ([\\PATH\datafiles\*.txt]) // or [\\PATH\datafiles\MyFile*.txt]

LOAD

From

$(File) (txt, codepage is 1252, no labels, delimiter is '\t', msq);

HtH

Roland

loading multiple files with wildcard

Wed, 29 Jun 2011 13:51:45 GMT

Hi Roland!

First I do a Mapping LOAD from Location22.txt

Then I do a LOAD, with an ApplyMap, from Transaction22.

This was for testing in a small scale. Now I ramping up and want all Location- and Transaction files.

That's when I used Location*.txt

And when my problem occured. Only the last file gets mapped.

Of cource I want it dynamic so I dont have to hardcode every file.

loading multiple files with wildcard

SureshUK — Wed, 29 Jun 2011 14:57:19 GMT

First load into a table using wild card and then try applymap on the resident table.

loading multiple files with wildcard

stevedark — Wed, 29 Jun 2011 15:44:01 GMT

You will need something like this:

Temp_Location:

LOAD

ID,

Name

FROM Location*.txt

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

Map_Location:

MAPPING LOAD

ID,

Name

RESIDENT Temp_Location;

DROP TABLE Temp_Location;

The map into the wildcard load from your transaction table should then work fine.

Regards,

Steve

QlikView Consultant

loading multiple files with wildcard

Miguel_Angel_Baeyens — Wed, 29 Jun 2011 15:58:17 GMT

Hi,

I agree with Steve (who has a very interesting post on mapping tables, by the way). The mapping load has some limitations the "normal" load does not. One of them is that it doesn't allow optimized loads and another one is that it doesn't allow either loading from multiple files using a wildcard, or joins, or so.

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

loading multiple files with wildcard

Thu, 30 Jun 2011 06:43:12 GMT

Steve, that's how I did it eventually. But somehow I did not make it work. Made the application work with satelite tables and I will look in to this as soon as I can. Thanks for all your replies.

loading multiple files with wildcard

Miguel_Angel_Baeyens — Thu, 30 Jun 2011 06:52:24 GMT

Hello Thomas,

Bear in mind that tables are concatenated implicitly when they have the same number and name of fields. You may be loading the same table twice and the records will be appended to the first load. Is there any chance that this might be happening?

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

loading multiple files with wildcard

stevedark — Thu, 30 Jun 2011 11:11:07 GMT

Following from Miguel's thought, could it be that you have duplicated ID's across the files? In which case you will want to do something like:

Temp_Location:

LOAD

replace(filebasename(), 'Location','') & ':' & ID as ID,

Name

FROM Location*.txt

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

You will then obviously need to do the same to the ID being mapped to from the Transactions file.

Hope that helps.

- Steve

loading multiple files with wildcard

Thu, 28 Jul 2011 06:06:21 GMT

What if we have multiple tables in one file and it is in *.xlsx format?

Re: loading multiple files with wildcard

stevedark — Thu, 28 Jul 2011 19:41:30 GMT

That is then a completely different question! It is possible to get a list of tabs within an Excel spreadsheet by connecting to it with an ODBC connection (a native QlikView connection will not work) and issuing a SQLTables statement. I suspect there will be information on this somewhere in the community.

What I have done in the past (to give more control over which tabs are loaded and what should be expected on each tab) is to have a sheet in the excel spreadsheet with a known tab (eg. Tab List). This can list the names of all the tabs in the spreadsheet - and give extra info if required. The load script can then load the list of tabs and enumerate around it (using a For vTab = 0 to NoOfRows('Table') - 1 loop) and read each tab name to a variable (using Peek) and then use the variable in the load.

Hope that helps.

Steve