Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gauravgg
Partner - Creator
Partner - Creator

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
sunny_talwar

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

];

View solution in original post

7 Replies
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

];


Capture.PNG

gauravgg
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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

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
Partner - Creator
Partner - Creator
Author

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