Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi can any one please help in acaheiving below output
Basically need to generate missing dates(Yearly) below is the input and expected o/p
ID | Date |
1 | 5/17/2020 |
1 | 6/24/2022 |
2 | 3/17/2015 |
2 | 6/2/2018 |
2 | 5/3/2019 |
2 | 9/24/2020 |
2 | 6/6/2022 |
3 | 1/1/2019 |
3 | 3/4/2021 |
3 | 5/8/2022 |
4 | 6/8/2021 |
O/P:
Output | ||
ID | Date | Comments |
1 | 5/17/2020 | |
1 | 5/17/2021 | Previous Date plus 1 year |
1 | 6/24/2022 | |
2 | 3/17/2015 | |
2 | 3/17/2016 | Previous Date plus 1 year |
2 | 3/17/2017 | Previous Date plus 1 year |
2 | 6/2/2018 | |
2 | 5/3/2019 | |
2 | 9/24/2020 | |
2 | 9/24/2021 | Previous Date plus 1 year |
2 | 6/6/2022 | |
3 | 1/1/2019 | |
3 | 1/1/2020 | Previous Date plus 1 year |
3 | 3/4/2021 | |
3 | 5/8/2022 | |
4 | 6/8/2021 |
Hi @naveen567 ,
you need to create a Master Calendar in the Load Script and associate it to your Date field as follows:
MasterCalendar: Load TempDate AS Date, week(TempDate) As Week, Year(TempDate) As Year, Month(TempDate) As Month, Day(TempDate) As Day, 'Q' & ceil(month(TempDate) / 3) AS Quarter, Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, WeekDay(TempDate) as WeekDay ; //=== Generate a temp table of dates === LOAD date(mindate + IterNo()) AS TempDate ,maxdate // Used in InYearToDate() above, but not kept WHILE mindate + IterNo() <= maxdate; //=== Get min/max dates from Field ===/ LOAD min(FieldValue('Date', recno()))-1 as mindate, max(FieldValue('Date', recno())) as maxdate AUTOGENERATE FieldValueCount('Date');
I agree, building a Master calender is the best idea.
Here is a good article to understand zu Master Calender.
https://qlikviewcookbook.com/2015/05/better-calendar-scripts/