Need to restrict last 6 months data from full load qvd

[Raju_6952]

Hi,

I have huge data in full load qvd and my requiement is to load only last 6 months of data from max of Start_TimeStamp field in qlik sense.

any suggestions would be highly appreciated.

Regards,

Raju

[Padma123]

table2:
load Max(month(date)) as max
Resident table1;

let vmax=Peek('max');

drop table table2;

NoConcatenate
t3:
load * Resident table1
where Month(date)>=$(vmax)-6;
drop table table1;

[Raju_6952]

Hi @Padma123 ,

When using variable condition its not restricting any data.

Regards,

Raju

[nina2]

Hello,

TABLE1:

LOAD * INLINE [
NUM, Start_TimeStamp
1, 02/08/2024
2, 25/08/2024
3, 05/04/2024
4, 02/05/2023
5, 31/07/2024
6, 29/01/2022
];

TABLE2:

LOAD date(max(Start_TimeStamp)) AS DATE
resident TABLE1;

let vmax=Peek('DATE');

drop table TABLE2;

NoConcatenate

TABLE3:
load * Resident TABLE1
where Start_TimeStamp >=addmonths('$(vmax)',-6);
drop table TABLE1;

[MatheusC]

@Raju_6952 
The first step would be formatting your field. You mentioned it is of the timestamp type, so it would be helpful to have a sample of the format. I'll suggest some options, and you can adjust them according to your data.

Date(Floor(Date(FieldTimestamp, 'YYYY-MM-DD hh:mm:ssZ')),'YYYY-MM-DD')

Date(Floor(Date#(FieldTimestamp, 'YYYY-MM-DD hh:mm:ssZ')),'YYYY-MM-DD')

or only

date(floor(FieldTimestamp))

So it would be something like

let vDate = MonthStart(Today(1),-6)  

Table:

                LOAD * FROM [....] ( qvd)  where date(floor(FieldTimestamp))>=’$( vDate)’;

As a suggestion, you might consider converting it to num() to make it a bit more performant.

num(MonthStart(Today(1),-6))

num(date(floor(FieldTimestamp)))

Regards,
Matheus

[marcus_sommer]

In regard to your statement of having huge data is your requirement to late for a sensible data-handling.

This means the appropriate steps should be done in beforehand, for example by:

• storing the max. timestamp within a table/variable at the time the qvd is created
• flagging the data with 0/1 flags and/or creating YYYYMM periods
• slicing the qvd into YYYYMM files
• replacing the timestamp with a date and a time field

The aim of such measurements is to be able to load the data like:

t: load * from qvd where exists(Field);

whereby the appropriate exists-data are loaded within a dummy-table directly in beforehand.

Another approach would be to load sliced qvd's with a filelist() loop and within the loop is another if-loop which queries the period from the file-name.

Of course the other here suggested methods will be working from a technically point of view but fetching the max. timestamp with an aggregation load and using this information within a following load in a normal where-clause which compares the condition on a record-level will take a lot of time by huge data-sets.