Autogenerate / dublicate based on missing Date Ran... - Qlik Community

ali-soy · ‎2022-07-27

Hi

I'm new in QlikSense.... I like to autogenerate missing date ranges from an existing table. I have invested a lot of time to find anything similar, but it was not possible for me to find a solution. I really appreciate for your support!

my existing table:

LOAD * INLINE
[ID, SDate, EDate, Value, Duration
1, 11.06.2022, 11.07.2022, A, 30
1, 12.07.2022, 11.08.2022, A1, 30
1, 19.08.2022, 19.08.2022, A2, 30
1, 19.08.2022, 31.12.9999, B1, 365
2, 01.01.2022, 31.01.2022, C1, 30
2, 31.01.2022, 01.03.2022, C2, 30
2, 02.03.2022, 01.04.2022, C3, 30
3, 05.06.2022, 05.07.2022, D1, 30
3, 09.09.2022, 09.10.2022, D2, 30];

In the table below you see what I expect to get with autogenerated records based on the previous EDate <> current SDate

The Script I'm using looks like this:

TempData:

LOAD * INLINE

[ID, SDate, EDate, Value, Duration

1, 11.06.2022, 11.07.2022, A, 30

1, 12.07.2022, 11.08.2022, A1, 30

1, 19.08.2022, 19.08.2022, A2, 30

1, 19.08.2022, 31.12.9999, B1, 365

2, 01.01.2022, 31.01.2022, C1, 30

2, 31.01.2022, 01.03.2022, C2, 30

2, 02.03.2022, 01.04.2022, C3, 30

3, 05.06.2022, 05.07.2022, D1, 30

3, 09.09.2022, 09.10.2022, D2, 30];

//Source: https://community.qlik.com/t5/Qlik-Sense-Documents/Understanding-the-Master-Calendar-video/ta-p/1479...

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(SDate) as minDate,

if(MAX(EDate)='31.12.9999',max(SDate +Duration), MAX(EDate)) as maxDate

Resident TempData;

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

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

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 as Num,

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

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

MasterCalendar:

Load

TempDate AS SelectDate,

week(TempDate) As Week,

Year(TempDate) As Year,

Date(TempDate,'MM') As Month,

Year(TempDate) &'.'&Date(TempDate,'MM') as YearMonth,

Day(TempDate) As Day,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Daten:

NoConcatenate

Load *,

0 as churn

resident TempData;

drop table TempData;

Inner Join IntervalMatch (SelectDate)

Load SDate, EDate

Resident Daten;

exit script;

ali-soy · ‎2022-07-29

Solution is...

TempData_0:
Load * , SDate as Data_date;
LOAD * INLINE
[ID, SDate, EDate, Product, Duration, Value
1, 11.06.2022, 11.07.2022,  A,  30,  1
1, 12.07.2022, 11.08.2022,  A1, 30,  1 
1, 19.08.2022, 19.08.2022,  A2, 30,  1
1, 19.08.2022, 31.12.9999,  B1, 365, 1
2, 01.01.2022, 31.01.2022,  C1, 30,  1
2, 31.01.2022, 01.03.2022,	C2, 30,  1 
2, 02.03.2022, 01.04.2022,  C3, 30,  1 
3, 05.06.2022, 05.07.2022,  D1, 30,  1
3, 09.09.2022, 09.10.2022,  D2, 30,  1];

TempData_1:
Load ID,SDate as Data_date Resident TempData_0;
Load ID,EDate as Data_date Resident TempData_0 where EDate <> '31.12.9999';
NoConcatenate
TempData_2: load distinct ID, Data_date Resident TempData_1 order by ID,Data_date asc;
left join Load Data_date, SDate, EDate, Product, Duration, Value Resident TempData_0;

ID_GROUP: LOAD DISTINCT ID AS ID_GROUP Resident TempData_0;

FOR I = 1 TO FieldValueCount('ID_GROUP');

ID_GROUP = FieldValue('ID_GROUP',I)

TempData:
LOAD 1 AutoGenerate 1;

NoConcatenate
TempData_3:
Load
	Data_date,
    If(IsNull(ID),peek('ID'),ID)  AS ID,
    If(IsNull(SDate),Data_date,SDate)  AS SDate, 
    If(IsNull(EDate),peek(SDate),EDate)  AS EDate, 
    Duration,
    Product,
    Value
Resident TempData_2
WHERE ID = '$(ID_GROUP)'
order by ID,Data_date desc;

NoConcatenate

Concatenate (TempData)
Load
	Data_date,
    ID,
    SDate, 
    EDate,
    If(IsNull(Product),peek(Product),Product)  AS Product,
    If(IsNull(Duration),peek(Duration) ,Duration)  AS Duration,
    If(IsNull(Value),0,Value)  AS Value
Resident TempData_3
WHERE not IsNull(EDate)
ORDER BY ID,Data_date asc; 

drop Tables   TempData_3;

NEXT I

drop Tables   TempData_2, TempData_1,TempData_0;

Autogenerate / dublicate based on missing Date Range from previous record

Script