Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Field from first table into Filepath for new table

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

4 Replies
Not applicable
Author

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

MayilVahanan

Hi

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

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

Not applicable
Author

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

;