Multiple Date specific QVD's data in 1 QVD with date

[nachiket_shinde]

Hello

I have multiple month end QVDs

eg.

Test20180131

Test20180228

Test20180331 and so on

Data doesn't have any date column.

I want to create 1 QVD 'TEST' , which will have data from all above QVDS. Additionally it will have date column which will be generated from date appended in original QVD

all data from Test20180131 will have date as 20180131

all data from Test20180228 will have date as 20180228  so on

Thanks in advance

Regards

[pradosh_thakur]

similar to this ??

else use a for loop to fetch all the files at once.

load *,DATE(date#(purgrchar(filename(),'Test'),'YYYYMMDD'),'YYYYMMDD') AS date

from your_file_path/Test20180131.qvd(qvd);

concatenate

oad *,DATE(date#(purgrchar(filename(),'Test'),'YYYYMMDD'),'YYYYMMDD') AS date

from your_file_path/Test20180228.qvd(qvd);

[YoussefBelloum]

Hi,

try this:

Table:

LOAD

.

.

right(FileBaseName(),8) as DATE

.

.

FROM Test*

STORE Table into 'path\table.qvd'(qvd);

[ben_pugh]

I would use a loop to load all of the files - this link should help you:

loop through to load all files from a folder and its subfolders?

and then load a calculated date field as in pradosh_thakur 's post above.

[OmarBenSalem]

do as follow:

Data :

load 0 as field AutoGenerate(0);

For Each vPath in FileList('lib://YourPath/*.qvd')

Concatenate

load *, date(date#(right('$(vPath)',8) , 'YYYYMMDD'),'YYYYMMDD') as date

FROM [$(vPath)]

(qvd);

Next vPath

It will loop through all the QVDs , bring all its data, and create ur date field, all the qvds will be concatenated in one final table.

[PawelB · Accepted solution]

Hi,

Here's my simple solution:

MyTable:

LOAD

   // Extract and convert from multiple filenames to date e.g.: Test20180131.qvd, Test20180228.qvd

    Date#(Left(Right(FileName(), 12),8), 'YYYYMMDD') as File_Name_Date,

    FileName() as MyField1

FROM [date_spec_qvds\*.qvd]

(qvd);

You can make even simpler version with Filebasename() function.

Here's an interesting article: Wildcard data loading (*.blah......)

[nachiket_shinde]

This won't work as I will be adding 1 file every month , so i will have to add 1 LOAD statement every month.

[YoussefBelloum]

nachiket.shinde.1981‌

try this with the FOR EACH  loop , it will work perfectly

[pradosh_thakur]

Hi Nachiknat

Nachiket Shinde wrote:

This won't work as I will be adding 1 file every month , so i will have to add 1 LOAD statement every month.

As i have already mentioned used a for loop to fetch all the file at once. The answer was just for two file you mentioned.