topic Re: Field from first table into Filepath for new table in QlikView

Field from first table into Filepath for new table

Mon, 11 Jul 2016 11:44:31 GMT

Hi all,

I want to be able to open a table based on the field that is pulled from a separate table, but am having no luck figuring it out. Any help would be appreciated.

Here's the example:

Calendar:

LOAD *,

@1 as Date,

textbetween(@2,' ','day')&textbetween(@2,' ',' ',2) as File

FROM

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

Data:

LOAD *

FROM

Re: Field from first table into Filepath for new table

Mon, 11 Jul 2016 11:57:21 GMT

Oh also I should have mentioned:

The Date field from the Calendar table denotes which folders the Data are in. But these don't seem to be working either.

The folder path would be: H:\year\month\file

Data:

LOAD *

FROM

Re: Field from first table into Filepath for new table

MayilVahanan — Mon, 11 Jul 2016 12:02:51 GMT

Try like this

Hope you have only one row in Calendar - File Field

Calendar:

LOAD *,

@1 as Date,

textbetween(@2,' ','day')&textbetween(@2,' ',' ',2) as File

FROM

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

Let vFilePath = Peek('File');

Data:

LOAD *

FROM

Re: Field from first table into Filepath for new table

Mon, 11 Jul 2016 12:18:43 GMT

Thanks for responding Mayil.

Actually there is a different file for each day of the year corresponding to 1 filename for each row. I then only want to do a lookup/mapping load (haven't figured out the best for this yet) to pull 1 or 2 fields from each file into a different table

Re: Field from first table into Filepath for new table

Mon, 11 Jul 2016 12:47:56 GMT

With your suggestion I have amended my script to look as follows, it is working fine to pull the year because all the years in the Calendar table are same at the moment (2016) however, it is receiving an error when it gets to pulling the month and file name.

Let vFilePath = Peek('File');

Let vYearPath = timestamp(peek('Date'),'yyyy');

Let vMonthPath = timestamp(peek('Date'),'mmm');

Data:

LOAD *

FROM

;