Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ?
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.
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 ?
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;
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;
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);
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);