topic Re: How to generate current and previous date qvds in QlikView

How to generate current and previous date qvds

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

Hi Experts,

Can any one please help me on below requirement.

I have date field in my data base like below

Date

10/04/2018 00:00:00

11/04/2018 00:00:00

12/04/2018 00:00:00

13/04/2018 00:00:00

14/04/2018 00:00:00

15/04/2018 00:00:00

16/04/2018 00:00:00

17/04/2018 00:00:00

Department:

Load

Date,

Departmentname,

Departmentid,

Salary,

Officeno;

Select

Date,

Departmentname,

Departmentid,

Salary,

Officeno

From eud.prod;

From the date field how to extract the max and previous dates data into 2 separate qvd's means

need to load 17/04/2018 00:00:00 max date into one qvd named Departmentcurrentdata.qvd and Previousdate i.e,16/04/2018 00:00:00 data into one more qvd named DepartmentPreviousdata.qvd.

While reloading the extraction script based on max and previous date need to generate the qvd dynamically.

Please help me on this.

Thanks in advance.

andrei.delta

stalwar1

Re: How to generate current and previous date qvds

YoussefBelloum — Fri, 20 Apr 2018 12:44:25 GMT

Hi,

on each reload you want to extract the max date and the previous date from the table Department and use these dates to generate QVD's ? and on the QVD you will store which fields ?

Re: How to generate current and previous date qvds

mahitham — Fri, 20 Apr 2018 12:52:29 GMT

Hi Youssef,

Thanks for your reply.

Yes if i reload the app after 2 days if the max date is 19/04/2018 then that qvd and previous qvd like 18/04/2018 need to extract.

The above total database table fields need to store in QVDs.

Re: How to generate current and previous date qvds

YoussefBelloum — Fri, 20 Apr 2018 12:58:02 GMT

I don't really understand you..

when you reload the script, even the variables will update with max and previous.

can you give a better example ?

Re: How to generate current and previous date qvds

mahitham — Fri, 20 Apr 2018 13:05:59 GMT

Hi Youssef,

By using the below script i can generate last 2days data into one qvd.

But in the source there is no today date so i am looking for how to pass max date into variable and how to extract max date and previous date data into two separate qvd's dynamically.

at the time of reload depends on max date and previous date need to generate the two separate qvd's using for loop.

Let vToday = Date(Today(),'DD/MM/YYYY hh:mm:ss');

Department:

Load

Date,

Departmentname,

Departmentid,

Salary,

Officeno where Date>=Today()-1 and Date<=Today();

Select

Date,

Departmentname,

Departmentid,

Salary,

Officeno

From eud.prod;

Re: How to generate current and previous date qvds

andrei_delta — Fri, 20 Apr 2018 13:39:57 GMT

Hi,

it's not so hard, but i can't write the full script right now...but i hope this works

Temp:

Distinct DateDist

from file.xls (whatever the file it is)

vMaxDate = peek ('DateDist',-1,'Temp'); //depends if the max date is the first or the last, in this case the last

maxdatetable:

load * from 'your table'

where Date = vMaxDate ;

store maxdatetable into Path\Departmentcurrentdata.qvd;

drop table maxdatetable;

previousdatetable:

load * from 'your table'

where Date <> vMaxDate;

store previousdatetable into Path\DepartmentPreviousdata.qvd.

drop table previousdatetable;

drop table Temp;

Re: How to generate current and previous date qvds

YoussefBelloum — Fri, 20 Apr 2018 13:59:46 GMT

EDITED

you can do something like this:

temp:

LOAD

max(date) as max

FROM eud.prod...

LET varMaxDate = Peek('max',0, 'temp');

LET varMaxDate_1 = Date($(varMaxDate)-1);

LET varMaxDate_1 = date(Peek('max',0, 'temp')-1);

DROP Table temp;

firstQVD:

LOAD

FROM eud.prod...

Where date=$(varMaxDate);

STORE firstQVD into 'path\firstQVD.QVD'(QVD);

SecondQVD:

LOAD

FROM eud.prod...

Where date=$(varMaxDate_1);

STORE SecondQVD into 'path\SecondQVD.QVD'(QVD);

Re: How to generate current and previous date qvds

Anonymous — Mon, 23 Apr 2018 06:01:26 GMT

try below,

Directory;

Store:

LOAD

Date,

Departmentname,

Departmentid,

Salary,

Officeno

FROM

[..\..\..\qvd\Products.qvd]

(qvd);

Max_Product:

LOAD

Date(max(Date)) as max_Date

Resident Store;

LET max_Date=Peek('max_Date');

DROP Table Store;

new:

Directory;

Date,

Departmentname,

Departmentid,

Salary,

Officeno

FROM

[..\..\..\qvd\Products.qvd]

(qvd);

Where Date>$(max_Date);