Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
bartvanwetten
Creator
Creator

Expand table between from-to dates

Hi, can anybody help me out on this one:

I would like the table you see int the attachment be expanded (via script!) with:

for every Location should be a record between his StartDate and Endate, and Plants filled with the corresponding number

Example:

For the first record I should have an extra amount of records (days between EndDate and StartDate) with Plants=502

Thanks again!

1 Solution

Accepted Solutions
sunny_talwar

May be like this

Plants_Temp:

LOAD * INLINE [

Id, Plants, StartDate, Location

1, 502,2015-10-22, 1

2, 585,2015-12-01, 1

3, 668,2016-01-01, 1

4, 760,2016-04-01, 2

5,  713,2016-06-15, 2

6, 713,2016-06-20, 2

7, 668,2017-02-01, 3

];

NoConcatenate

Plants_Temp2:

LOAD *

Resident Plants_Temp order by Location, StartDate desc;

DROP Table Plants_Temp;

Plants:

NoConcatenate

LOAD *,

  Date(StartDate + IterNo() - 1) as Date

While StartDate + IterNo() - 1 <= EndDate;

LOAD *,

    Date(if(Location=previous(Location),previous(StartDate),now()),'DD-MM-YYYY') as EndDate

Resident Plants_Temp2;

   

DROP Table Plants_Temp2

View solution in original post

4 Replies
sunny_talwar

May be like this

Plants_Temp:

LOAD * INLINE [

Id, Plants, StartDate, Location

1, 502,2015-10-22, 1

2, 585,2015-12-01, 1

3, 668,2016-01-01, 1

4, 760,2016-04-01, 2

5,  713,2016-06-15, 2

6, 713,2016-06-20, 2

7, 668,2017-02-01, 3

];

NoConcatenate

Plants_Temp2:

LOAD *

Resident Plants_Temp order by Location, StartDate desc;

DROP Table Plants_Temp;

Plants:

NoConcatenate

LOAD *,

  Date(StartDate + IterNo() - 1) as Date

While StartDate + IterNo() - 1 <= EndDate;

LOAD *,

    Date(if(Location=previous(Location),previous(StartDate),now()),'DD-MM-YYYY') as EndDate

Resident Plants_Temp2;

   

DROP Table Plants_Temp2

malini_qlikview
Creator II
Creator II

Hi ,

You create a calendar and use IntervalMatch function to generate the dates and values,

Use the below script after your script statement and see the result

MinMaxDate:

Load

min(StartDate) AS MinDate,

Max(EndDate) AS MaxDate

Resident Plants;

Calendar:

Load

Date(MinDate+IterNo()-1) AS Date

Resident MinMaxDate

while MinDate+IterNo()-1<=MaxDate;

Drop Table MinMaxDate;

IntervalMatch (Date) LOAD StartDate, EndDate Resident Plants;

bartvanwetten
Creator
Creator
Author

Hi Sunny,

in 10 minutes man! Are there moment you don't spend with Qlik?

Cheers mate!

Bart

bartvanwetten
Creator
Creator
Author

Thanx for your input Malini!