topic Re: Load multiple similar files except one in QlikView

Load multiple similar files except one

Anonymous — Fri, 20 Jan 2017 07:26:04 GMT

Hi Guys,

I have 4 table files:

T7PLD1_600,

T7PLD1_604,

T7PLD1_610,

T7PLD1_PH3,

And map to these tables:

Map_Gmina:

mapping load

NIP00 as NIP,

ApplyMap('Gmina_Klucz',REGIO & '-' & COUNC & '-' & TERY2,0) as Gmina_Nazwa

FROM $(IBS_QVD)T7PLD1_*.qvd(qvd)

WHERE ApplyMap('Gmina_Klucz',REGIO & '-' & COUNC & '-' & TERY2,0)<>0;

The main problem is that i would like to read all tables except T7PLD1_610.

How to do this in the simplest way?

Best wishes,

Jacek Antek

Re: Load multiple similar files except one

marcus_sommer — Fri, 20 Jan 2017 07:41:35 GMT

It's not possible to exclude a file by using a (to wide specified) wildcard. To solve such a task you will need to load the files with a loop through a filelist() and which you could extend with various checkings, for example to the filename. Within the help by "for each ..." is a good example of it and also here: Loops in the Script.

Beside them you shouldn't load a mapping-table with a wildcard or any other loop. Better is to load these files at first normal and afterwards you creates the mapping-table with a resident load on your previous loaded temp-table.

- Marcus

Re: Load multiple similar files except one

Anonymous — Fri, 20 Jan 2017 08:18:58 GMT

Ok thank you Marcus for your help.

This is difficult I see using this filelist().

But i can imagine how this hould work:

1. Define list of files

2. do a loop

I will try it and return with results here.

Why mapping through a few tables is a bad idea? It is slower or what?

Best Wishes

Jacek Antek

Re: Load multiple similar files except one

Anonymous — Fri, 20 Jan 2017 08:25:32 GMT

How to do within filelist() not an adress but inline table with adresses?

For example for 3 tables :

Set Variable =

$(IBS_QVD) T7PLD1_600,

$(IBS_QVD) T7PLD1_604,

$(IBS_QVD) T7PLD1_PH3

For each file in FileList(Variable)

TEMP_FILE:

LOAD *

Next;

something like that?

Jacek

Re: Load multiple similar files except one

marcus_sommer — Fri, 20 Jan 2017 09:43:10 GMT

Try it in this way:

For each file in FileList('Path\FilePrefix*.qvd')

if subfield('$(file)', '\', -1) <> 'T7PLD1_610.qvd' then

TEMP_FILE:

LOAD * From [$(file)] (qvd);

end if

Next

Map_Gmina:

mapping load

NIP00 as NIP,

ApplyMap('Gmina_Klucz',REGIO & '-' & COUNC & '-' & TERY2,0) as Gmina_Nazwa

Resident TEMP_FILE

WHERE ApplyMap('Gmina_Klucz',REGIO & '-' & COUNC & '-' & TERY2,0)<>0;

- Marcus

Re: Load multiple similar files except one

Anonymous — Fri, 20 Jan 2017 10:11:03 GMT

Marcus,

thank you for your help.

I know how to do this:

Map_Gmina:

mapping load

NIP00 as NIP,

ApplyMap('Gmina_Klucz',REGIO & '-' & COUNC & '-' & TERY2,0) as Gmina_Nazwa

Resident TEMP_FILE

WHERE ApplyMap('Gmina_Klucz',REGIO & '-' & COUNC & '-' & TERY2,0)<>0;

- but i don't know why I should this use? What is purpose?

Jacek

Re: Load multiple similar files except one

marcus_sommer — Fri, 20 Jan 2017 10:26:52 GMT

A mapping-table is a special kind of a temporary table - you couldn't see them and access them in any way unless with the few specified mapping-functions respectively mapping-statements. Because of these special logic there are some further features like them that each single load-run will create an own mapping-table and not auto-concateanted like other tables with the same data-structure. This meant your mapping-table would only contain the data from the very first load and therefore an applymap() on them wouldn't return the expected values.

- Marcus