Dump Data after every 10 days in incremental Load

[Anonymous]

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

[jonathandienst]

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.

[Anonymous]

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

[jonathandienst]

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;

[Anonymous]

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

[karthikoffi27se]

Hi Shweta,

Yeah, It will fetch the last 7 days

Many Thanks

Karthik

[Anonymous]

i need to fetch last 10 days irrespective of the fact whatever the  data is present

[Anonymous]

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

[Anonymous]

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