how to split one record into multipal rows based on date?

[gauravgg]

hi all

i have a table which has a unique id (ID) ,amount ,StartDate,EndDate.

I have to check the StartDate and EndDate

i.e  if a row contains ID=001 ,  StartDate = 01-jan-2016 and EndDate 01-08-2016

ID	StartDate	EndDate	Amount
001	01-Jan-2016	01-Mar-2016	1000

I want to split that row by month

i.e    

ID	StartDate	EndDate	Amount
001	01-Jan-2016	01-Feb-2016	1000
001	01-Feb-2016	01-Mar-2016	1000

How i can split the the rows while loadling ? or is there any other way to do ?

can anyone help me

thanks in advance

[sunny_talwar]

May be like this:

Table:

LOAD ID,

  Date(AddMonths(StartDate, IterNo() - 1)) as StartDate,

  Date(AddMonths(StartDate, IterNo())) as EndDate,

  Amount

While AddMonths(StartDate, IterNo() - 1) < EndDate;

LOAD * INLINE [

    ID, StartDate, EndDate, Amount

    001, 01-Jan-2016, 01-Mar-2016, 1000

];

[gauravgg]

hi sunny

it is not working, it is saying  0 records fetch

[sunny_talwar]

Is your date getting read correctly by Qlik Sense?

Why don’t my dates work?

Get the Dates Right

[gauravgg]

it's working

but if StratDate =05-Jan-2016 EndDate= 01-Mar-2016

it should split like

ID    StartDate     EndDate          Amount

001  05-Jan-2016  01-Feb-2016    1000

001 01-Feb-2016   01-Mar-2016    1000

[sunny_talwar · Accepted solution]

Here you go

Table:

LOAD ID,

  Date(If(IterNo() = 1, StartDate, MonthStart(StartDate, IterNo() - 1))) as StartDate,

  Date(MonthStart(StartDate, IterNo())) as EndDate,

  Amount

While AddMonths(StartDate, IterNo() - 1) < EndDate;

LOAD * INLINE [

    ID, StartDate, EndDate, Amount

    001, 05-Jan-2016, 01-Mar-2016, 1000

];

[gauravgg]

Thanks Sunny

it is working

[sunny_talwar]

Awesome, I am glad we finally got what you were looking for. Please close this thread by marking the correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny