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: 
nachiket_shinde
Contributor III
Contributor III

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

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

1 Solution

Accepted Solutions
PawelB
Contributor II
Contributor II

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......)

View solution in original post

8 Replies
pradosh_thakur
Master II
Master II

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);

Learning never stops.
YoussefBelloum
Champion
Champion

Hi,

try this:

Table:

LOAD

.

.

right(FileBaseName(),8) as DATE

.

.

FROM Test*



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

ben_pugh
Creator
Creator

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
Contributor II
Contributor II

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
Contributor III
Contributor III
Author

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
Champion
Champion

nachiket.shinde.1981

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

pradosh_thakur
Master II
Master II

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.

Learning never stops.