topic Re: How to avoid loading all the Qvds...?? in QlikView

How to avoid loading all the Qvds...??

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

Hi All,

we are following 3 tier architecture in our project...

1.Extractor

2. Data Model layer/Transformation layer

3.Report/Dashboard

Excel files are source to our extractor, every month we get one new excel file into a source path from where we extract the etract them into excel and we generate Qvds, in next layer they will be transformed and again stored in another path, we use these Qvds as source to our final Report/Dashboard...

now the problem is if any excel files(Raw data) is having wrong data then we have to replace it with correct excel file and then have to reload all 3 apps for all months...which is a difficult task. so we are thinking of an alternate approch that if any Excel file is wrong after our monthly run, then once we replace it with a correct excel file all the 3 apps has to reload for only that Month....

is there any way to achive this...??

please suggest... thanks in advance

Re: How to avoid loading all the Qvds...??

Anil_Babu_Samineni — Tue, 30 Oct 2018 02:49:33 GMT

We can play with Files where Format of the file and either this is excel / CSv. But we can't play with Data which holding inside the files. Probably you may get back to the team how flow goes to folder there you can request.

Re: How to avoid loading all the Qvds...??

dhasharadh — Tue, 30 Oct 2018 09:18:51 GMT

Hi Anil, thanks for the reply..

okay understood, can we atleast stop reloading all the Qvds at report level...till my ETL app if it is reloaded for all months its okay, but my report app needs to pick only the changed Qvd..

can we achieve this using date of modified in the Qvd path like every time whenever its reloaded it has to pick the Qvd based its generation data in that Qvd path...

Re: How to avoid loading all the Qvds...??

rwunderlich — Tue, 30 Oct 2018 11:41:22 GMT

I've done this before for customers. I don't have a code example at the moment, but here's the logic:

1. In your Extractor, loop through the excel files using "for each file in filelist(...)". Check the filetime(), if the excel file is newer than the extract QVD (or extract QVD is missing), then do the extract for that file.

2. Do the same thing for the Transform layer, comparing the extract QVD filetime() to the transform QVD filetime().

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

Re: How to avoid loading all the Qvds...??

dhasharadh — Tue, 30 Oct 2018 13:28:13 GMT

Thanks Much Rob Wunderlich !!

Got some hope with your words, understood that it is possible to implement but bit confused on how to use 'for each file in filelst()'

Except using filetime() am not able to use anything in a correct way, it would be great if you provide some code with below example..

my excel is like below, which i pull into excel..Thanks Again !!!

LOAD [Emp ID],
Sal,
Location,
Month,
FROM

(ooxml, embedded labels, table is [20180501]);

Re: How to avoid loading all the Qvds...??

rwunderlich — Wed, 31 Oct 2018 20:06:29 GMT

There are a couple of ways to approach it. Here is one approach. The script loads *.txt files, just adapt it for your xls files and names. You can also adapt it for extract to transform qvd. If you ever want to re-generate any qvd, just delete the qvd.

Filelist:

First 1

LOAD

Filepath() as FilePath,

'ExtractQVD\' & FileBaseName() & '.qvd' as QvdName

FROM [*.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

Where FileTime() > alt(FileTime('ExtractQVD\' & FileBaseName() & '.qvd'),0)

;

For idx = 0 to NoOfRows('Filelist')-1

Let vFilepath = peek('FilePath', $(idx), 'Filelist');

Let vQvdName = peek('QvdName', $(idx), 'Filelist');

Data:

LOAD

From [$(vFilepath)]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

STORE Data INTO [$(vQvdName)] (qvd);

DROP TABLE Data;

NEXT idx

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com