topic Re: how to split one record into multipal rows based on date? in App Development

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

gauravgg — Fri, 07 Oct 2016 18:06:41 GMT

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

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

sunny_talwar — Fri, 07 Oct 2016 18:11:24 GMT

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

];

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

gauravgg — Fri, 07 Oct 2016 18:24:38 GMT

hi sunny

it is not working, it is saying 0 records fetch

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

sunny_talwar — Fri, 07 Oct 2016 18:26:52 GMT

Is your date getting read correctly by Qlik Sense?

Why don’t my dates work?

Get the Dates Right

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

gauravgg — Fri, 07 Oct 2016 18:34:36 GMT

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

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

sunny_talwar — Fri, 07 Oct 2016 18:49:21 GMT

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

];

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

gauravgg — Sat, 08 Oct 2016 06:13:56 GMT

Thanks Sunny

it is working

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

sunny_talwar — Sat, 08 Oct 2016 07:45:30 GMT

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