topic Re: Create Single date using From & To date in QlikView

Create Single date using From & To date

koushik_btech20 — Mon, 14 Sep 2015 07:43:48 GMT

I have company and department and from & To date wise target data. I have to create a single date. I have posted a demo data in excel and the the desired output in the excel and also a qvw app. Please check & revert if anyone have any solution.

With Regards

Koushik

Re: Create Single date using From & To date

sunny_talwar — Mon, 14 Sep 2015 08:28:00 GMT

I think you will find IntervalMatch useful here: IntervalMatch

Re: Create Single date using From & To date

qlikviewwizard — Mon, 14 Sep 2015 08:32:25 GMT

Hi,

Check this link. Hope this will help you.

Creating a Date Interval from a Single Date

Interval Match Feature/Function

Re: Create Single date using From & To date

sunny_talwar — Mon, 14 Sep 2015 08:37:09 GMT

See if this script helps:

Table:

LOAD Company_id,

Department_id,

DateFrom,

DateTo,

Target

FROM

StartDateEndDate.xlsx

(ooxml, embedded labels, table is Data);

Temp:

Load min(DateFrom) as minDate,

max(DateTo) as maxDate

Resident Table;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD Date($(varMinDate) + IterNo() - 1) as Date

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Join (Table)

IntervalMatch(Date)

LOAD DateFrom,

DateTo

Resident Table;

Re: Create Single date using From & To date

tresB — Mon, 14 Sep 2015 08:37:46 GMT

Try:

Load

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

From <> While DateFrom+IterNo()<=DateTo;