Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Not applicable

loading multiple files with wildcard

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

10 Replies
Highlighted
Not applicable

loading multiple files with wildcard

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

Highlighted
Not applicable

loading multiple files with wildcard

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.

Highlighted
New Contributor II

loading multiple files with wildcard

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

Highlighted
MVP & Luminary
MVP & Luminary

loading multiple files with wildcard

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

Highlighted

loading multiple files with wildcard

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

Highlighted
Not applicable

loading multiple files with wildcard

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.

Highlighted

loading multiple files with wildcard

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

Highlighted
MVP & Luminary
MVP & Luminary

loading multiple files with wildcard

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

Highlighted
Not applicable

loading multiple files with wildcard

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