topic Re: Multiple Date specific QVD's data in 1 QVD with date in QlikView

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

nachiket_shinde — Wed, 25 Nov 2020 16:16:04 GMT

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

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

pradosh_thakur — Thu, 25 Jan 2018 14:02:17 GMT

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

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

YoussefBelloum — Thu, 25 Jan 2018 14:08:40 GMT

Hi,

try this:

Table:

LOAD

right(FileBaseName(),8) as DATE

FROM Test*

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

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

Ben_P — Thu, 25 Jan 2018 14:37:35 GMT

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.

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

OmarBenSalem — Thu, 25 Jan 2018 15:01:22 GMT

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.

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

PawelB — Mon, 29 Jan 2018 10:27:42 GMT

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

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

nachiket_shinde — Tue, 30 Jan 2018 10:43:38 GMT

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

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

YoussefBelloum — Tue, 30 Jan 2018 11:03:39 GMT

nachiket.shinde.1981‌

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

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

pradosh_thakur — Tue, 30 Jan 2018 13:28:03 GMT

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.