Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm receiving Excel data from mailbox every day and storing that data in a QVD , but no date field in that. After one moth data storing into QVD is more than 30 days, I need only last 30 days data. How to limit number of rows in QVD while storing data into QVD.
IN:
LOAD
Company,
date(today())as Date_in ,
count([Incident ID]) as 'Total Incidents'
FROM
(ooxml, embedded labels, table is Rawdata) Group by Company,date(today());
store IN into D:/INID.qvd;
Regards,
Krishna
Hi Krishna,
Maybe something like this... I've put ome comments in to explain
IN:
LOAD
Company,
date(today())as Date_in ,
count([Incident ID]) as 'Total Incidents'
FROM
(ooxml, embedded labels, table is Rawdata)
Group by Company,date(today());
//Load from existing QVD file and add to table you're currently loading...
Concatenate (IN) Load * From D:/INID.qvd (qvd)
//Where the date you've created is in the last 30 days
Where Date_in > Date(Today()-30)
//Make sure you're not doubling up data in case of multiple reloads in the day for whatever reason
//This is ok, because you're grouping by date anyway so you'll only have one row per date.
and Not Exists(Date_in);
//Re-write all old data in the last 30 days but also the new data, all in the IN table
//basically overwriting and adding new data...
store IN into D:\INID.qvd (qvd);
Hope this helps,
Sam
Hi Krishna,
Create a temporary table and store that. something like.
Noconcatenate
TempIN:
Load * resident IN Where Date_in ...(your condition here);
Store TempIN into D:/INID.qvd;
DROP Table TempIN ;
Regards
Andrew