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: 
koushik_btech20
Creator
Creator

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
sunny_talwar

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;

View solution in original post

4 Replies
sunny_talwar

I think you will find IntervalMatch useful here: IntervalMatch

qlikviewwizard
Master II
Master II

sunny_talwar

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;

tresesco
MVP
MVP

Try:

Load

      *,

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

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