QVD PIT time only pulling data from day before but have more history

[Fred-Zabala]

Good Afternoon

Brief history of what we are doing: We have QVD files dating all the way to 2018. We are transforming this on QLIK into PIT QVD, which is then named PIT_<sub>_20211109

When we load our script to compile these data into the QVD, it only does the day before. We have different clients, but we use the same script (and same amount of history), but it would be always the day before it starts.

I know its abit convulted, but i hope you get the gist. Here is the top script, and one of the tables

Let varMinDate = Num(MonthStart(Addmonths($(vTodaysDateNum),-24)));
Let varLast12Month = Num(MonthStart(Addmonths($(vTodaysDateNum),-12)));
Let varMaxDate = floor(Num(MonthEnd(AddMonths(($(vTodaysDateNum)),-1))));

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num
AutoGenerate 1
While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

PITLoadCalendar:
Load Distinct
Date(Num) as PIT_Dates_L24M,
// if(Num>=$(varLast12Month),Date(Num)) as PIT_Dates_L12M,
if(Num>=$(varLast12Month) and Num = Floor(WeekEnd(Num)),Date(Num)) as PIT_Dates_L12M_WeekEndOnly,
if(Num>=$(varLast12Month) and Num = Floor(WeekEnd(Num)),(Num)) as PIT_Dates_L12M_WeekEndOnlyNum
Resident TempCalendar
Order By Num ASC;

Drop Table TempCalendar;

Let varMinDate = Null();
Let varLast12Month = Null();
Let varMaxDate = Null();

And one of the tables straight afterwards

History_Properties:
LOAD
PMP_SEQ,
[PIT Area SQM] as [Area SQM],
[PIT Opex PA] as [Opex PA],
[PIT Carpark Rent PA] as [Carpark Rent PA],
[PIT Rent PA] as [Rent PA],
[PIT Property Type] as [Property Type],

[PIT Prop Code] as [Property Code],
[PIT Property Status] as [Property Status],
if (match([PIT Property Status],'Current','Active'),1,0) as [PIT Active Property Flag],

[PIT Prop Date],
MonthName([PIT Prop Date]) as [PIT Prop Month Year],
floor(Num(MonthEnd("PIT Prop Date"))) as "PIT Prop MonthEnd",

1 as [PIT Property Flag],
1 as PITLastDayOfTheMonthProp,
'PIT' as [Property Data Type]
FROM [$(varQVDPath)/2. Transformed/PIT/Monthly/PIT_Property_*.qvd](qvd)
where Exists([PIT_Dates_L24M],[PIT Prop Date]);

Call StoreAndDropTable('History_Properties');

Thanks

[Gysbert_Wassenaar]

Check that the date formats of the fields PIT_Dates_L24M and PIT Prop Date are always the same.
Check that PITLoadCalendar does contain all the dates you expect it should contain.
Check that tempCalendar generates the correct dates.
Check that the variables varMinDate, varLast12Month, varMaxDate and vTodaysDateNum all have the correct values.