Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
gauravgg
Contributor

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

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

IDStartDateEndDateAmount




00101-Jan-201601-Mar-20161000

I want to split that row by month

i.e    

IDStartDateEndDateAmount
00101-Jan-201601-Feb-20161000
00101-Feb-201601-Mar-20161000

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

can anyone help me

thanks in advance

1 Solution

Accepted Solutions

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

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

];

7 Replies

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

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

];


Capture.PNG

gauravgg
Contributor

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

hi sunny

it is not working, it is saying  0 records fetch

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

Is your date getting read correctly by Qlik Sense?

Why don’t my dates work?

Get the Dates Right

gauravgg
Contributor

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

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?

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
Contributor

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

Thanks Sunny

it is working

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

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