Generating records in between dates

[dusasuman]

Hi community,

Here i am attaching an excel file of sample data. I have a requirement that i need to repeat the same record until it encounters the new date in date field.

In the attached excel file sheet 'Input' the input source data. and what i should get in data model is mentioned in 'Output'.

Please help me.

[sunny_talwar]

Is this what you want?

Script:

Table:

LOAD Date,

    Unit,

    Branch,

    [Activity Code]

FROM

betweenrecords.xlsx

(ooxml, embedded labels, table is Input);

FinalTable:

NoConcatenate

LOAD Date(Date + IterNo() - 1) as Date,

  Unit,

  Branch,

  [Activity Code]

While Date + IterNo() - 1 < EndDate;

LOAD *,

  Date(If(Unit = Previous(Unit), Previous(Date) - 1, Today())) as EndDate

Resident Table

Order By Date desc;

DROP Table Table;

[dusasuman]

In the source data excel file the 1st sheet is input data and in the 2nd sheet i have mentioned the output that finally i should get..

[dusasuman]

Hi sunny

thanks for your effort. The result is very close. But the record with 5/1/2016 missing and it is not generating. Also till the end date the records are not generating i.e records are generating till 9/30/2016. Need to complete up to 10/2/2016 with Activity code 'S', Branch Number 10..

[dusasuman]

Sunny...

Thanks.. Got it, just changed the code. Instead today() i kept Date field and in the while loop included <=..

Got the final results..