Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi have list of qvd stored on daily basis.Data is present on day -1 basis.If today is 04/08/17 then data of 03/08/17 is present .
As there is no load on Saturday so data of 03/08/17 & 04/08/17 is same. I have data of 12 days .and i amusing concate function in my incremental how can I dump data after every 10 days
Please find the sample data
Use a pattern like this:
Fact:
LOAD ID,
Date(Today()-1) as Date,
...
FROM LatestDay;
Concatenate(Fact)
LOAD *
FRom Incremental.qvd (qvd)
Where Date >= Today() - 11;
STORE Fact Into Incremental.qvd (qvd);
Adjust for your actual field and table names. If you want to consider workdays, then use the workday functions FirstWorkDate()/LastWorkDate()/NetworkDays() in your where clause as required.
Cool,onemore thing while storing the data in qvd .I want to store only last 10 days qvd and move rest of qvd into backup folder
Something like this BEFORE the running the previous code:
Backup:
LOAD *
FROM Incremental.qvd (qvd)
Where Date < Today() - 11;
Concatenate (Backup)
LOAD *
FROM Backup.qvd (qvd);
STORE Backup Into Backup.qvd (qvd);
DROP Table Backup;
There may be case that some of date are missing in the database. So instead of fetching the last 10 days it will fetch last 7 days
03/09/2017 |
02/09/2017 |
31/08/2017 |
30/08/2017 |
29/08/2017 |
28/08/2017 |
27/08/2017 |
24/08/2017 |
23/08/2017 |
22/08/2017 |
31/07/2017 |
30/06/2017 |
31/05/2017 |
30/04/2017 |
31/03/2017 |
28/02/2017 |
Hi Shweta,
Yeah, It will fetch the last 7 days
Many Thanks
Karthik
i need to fetch last 10 days irrespective of the fact whatever the data is present
FOR EACH vFile IN FileList('$('*.qvd')
FileList:
LOAD
'$(vFile)' AS FileName_WithPath,
Date(MakeDate(Right(SubField(SubField('$(vFile)','_',-1),'.',1),4),mid(SubField(SubField('$(vFile)','_',-1),'.',1),3,2),left(SubField(SubField('$(vFile)','_',-1),'.',1),2)),'DD-MM-YYYY') AS FileDate
AUTOGENERATE 1;
NEXT vFile;
INNER JOIN(FileList)
LOAD
Max(FileDate,10) AS FileDate2
RESIDENT FileList; //For finding the latest file.
LET vPrevFileDate = num(Date(Peek('FileDate2',0,'FileList'),'DDMMYYYY'));///
this will give last 10th day ,irrespective of of the missing day
it will not help as i want to keep the data of last 10 days for current month and for past months it will give last day of month for example if I select Sep it it will give 1,2,3,4 ,5,6 in days and if i select august it should give 28,29 30,31 and if select july jun,may etc it should give only 31 in day filter