topic Re: Expand table between from-to dates in QlikView

Expand table between from-to dates

bartvanwetten — Mon, 10 Apr 2017 13:04:11 GMT

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!

Re: Expand table between from-to dates

sunny_talwar — Mon, 10 Apr 2017 13:09:41 GMT

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

Re: Expand table between from-to dates

malini_qlikview — Mon, 10 Apr 2017 13:19:09 GMT

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;

Re: Expand table between from-to dates

bartvanwetten — Wed, 12 Apr 2017 07:23:45 GMT

Hi Sunny,

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

Cheers mate!

Bart

Re: Expand table between from-to dates

bartvanwetten — Wed, 12 Apr 2017 07:28:32 GMT

Thanx for your input Malini!