How to generate current and previous date qvds

[mahitham]

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

[YoussefBelloum]

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 ?

[mahitham]

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.

[YoussefBelloum]

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 ?

[mahitham]

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;

[andrei_delta]

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;

[YoussefBelloum]

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

[Anonymous]

Hi

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

FROM

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

(qvd);

Where Date>$(max_Date);