Limiting the number of rows in QVD while loading data

[Anonymous]

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

[sam_grounds]

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

[effinty2112]

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