Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

koushik_btech20
Contributor

Create Single date using From & To date

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

1 Solution

Accepted Solutions

Re: Create Single date using From & To date

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;

4 Replies

Re: Create Single date using From & To date

I think you will find IntervalMatch useful here: IntervalMatch

Arjunarao
Honored Contributor II

Re: Create Single date using From & To date

Re: Create Single date using From & To date

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;

MVP
MVP

Re: Create Single date using From & To date

Try:

Load

      *,

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

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

Community Browser