Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
I think you will find IntervalMatch useful here: IntervalMatch
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;
Try:
Load
*,
Date(DateFrom+IterNo()-1) as Date
From <> While DateFrom+IterNo()<=DateTo;