Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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 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
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