topic Load certain data in QlikView

Load certain data

vipin_mishra479 — Thu, 02 Jun 2016 13:48:48 GMT

Hi All,

I have load my qvd on daily basis like (A_01/06/2016.qvd, A_02/06/2016.qvd,......) load last 2 month data

and i need to load ony last 28 days qvd how can i load last 28 days Qvd in my qvw file.

Re: Load certain data

fvelascog72 — Thu, 02 Jun 2016 13:55:14 GMT

Hi Vipin,

You can read files names and select by date which ones you want to load.

Regards,

Re: Load certain data

trdandamudi — Thu, 02 Jun 2016 13:55:45 GMT

If this exercise is one off then you can have all your 28 days worth of .QVD files in a folder and load it up.

Re: Load certain data

trdandamudi — Thu, 02 Jun 2016 13:58:35 GMT

Below is a sample load statement for all the .QVDs in a folder...

QVD_Data:

LOAD ID,

Start_Date,

Amount

FROM

;

Re: Load certain data

tamilarasu — Thu, 02 Jun 2016 14:37:17 GMT

Hi Vipin,

I have a few questions.

* You can not store the qvd with the special characters like slash ( / ) . What is your exact file names.?

* What about the holidays and weekends.?

* Do you want to load only the latest 28 files from the available qvd's.?

Please elaborate your question a bit more.

Re: Load certain data

vipin_mishra479 — Thu, 02 Jun 2016 14:37:28 GMT

Thanks for the quick reply

I am loading data daily basis so that i have a 2 months of data but

when i load like this C:\Source_Files\*.qvd;

Then i get all 2month qvd file but i need only 28 day qvd

Re: Load certain data

vipin_mishra479 — Thu, 02 Jun 2016 14:40:57 GMT

Hi Tamil,

vDateVar = Today();

I am load application every monday to friday and load like C:\Source_Files\A_$(vDateVar ).qvd;

So When ever my file are run automatically save qvd on basis of date.

And yes i want only load latest 28 days of qvd.

Re: Load certain data

trdandamudi — Thu, 02 Jun 2016 14:47:31 GMT

Vipin,

As i mentioned if this is one off exercise, then copy those 28 days worth of .qvds to a separate folder and load it from there.

Re: Load certain data

vipin_mishra479 — Thu, 02 Jun 2016 15:00:05 GMT

Hi Thirumala,

that is the manual process to copy 28 days qvd into separate folder. but i want to do this dynamicaly

Re: Load certain data

tamilarasu — Thu, 02 Jun 2016 15:16:01 GMT

Vipin,

Check the below script. Don't forgot to change the date format in FileNames table .

FileNames:
First 1 LOAD 
FilePath() as FileName,
Date(Date#(Right(FileBaseName(),10),'DD_MM_YYYY')) as Date
FROM [*.qvd] (qvd);

NoConcatenate
Sort:
Load Rowno() as Row, 
FileName,
Date
Resident FileNames order by Date desc; 

Last28days:
Load Row,
FileName 
Resident Sort Where Row <=28;

DROP Table FileNames, Sort;

For i =1 to  NoOfRows('Last28days')-1

Let vFile = Peek('FileName',$(i)-1,'Last28days');
Data:
LOAD *, FileName() as FileName
From [$(vFile)] (qvd);

Next i

DROP Table Last28days;

Re: Load certain data

Anonymous — Thu, 02 Jun 2016 15:28:26 GMT

Hi Vipin,

Try this code:

SET vs_Route = 'YourRoute';

For Each File IN FileList('$(vs_Route)'&'A_??/??/????.qvd')

FileListAux:

LOAD Right(FileName('$(File)'),16) as FileName,

MakeDate(

Mid(Right(FileName('$(File)'),16),9,4), //Year

Mid(Right(FileName('$(File)'),16),6,2), //Month

Mid(Right(FileName('$(File)'),16),3,2)) /*Day*/ as FileDate

AutoGenerate 1;

Next File

MaxDate:

LOAD MAX(FileDate) as MaxDate RESIDENT FileListAux;

LET vs_d_MaxDate = DATE(Peek('MaxDate',0,'MaxDate'),'DD/MM/YYYY');

NoConcatenate

FileList:

LOAD * RESIDENT FileListAux WHERE FileDate = '$(vs_d_MaxDate)'-28;

IF NoOfRows('FileList')>0 THEN

For x=0 to NoOfRows('FileList')-1

LET vs_File = '$(vs_Route)'&Peek(FileName,$(x),FileList);

Data:

LOAD * FROM $(vs_File) (qvd);

END IF;