Dynamically loading data every year. And saving it in separate files

[ritumishra01]

Hi ,

Right now i have a pp which is loading data for every year via static way . we have defined date variables

let vStart = '7/1/2023 00:00';
let vEnd = '6/30/2024 23:59';

and then we have different sections in SQL in qlik where we are calling data based on the below examples

where encsat.EncEffDttm >= '$(vStart)' and encsat.EncEffDttm <= '$(vEnd)'

and then we are storing all the files

STORE enc3 INTO [lib://CertifiedDataModels/ClinicalDataModel/Encounters_fy24.QVD] (qvd);

we are segregating all the files based on years. and all the data from 2017 is getting saved this way.

but as we have defined variable so we need to come back every year and change the date range and also the store path so that files get separately saved and later we call the data from 2017 in the main app.

as we are calling date filters in sql query if we try to load data from 2017 - 2023 it is taking huge time that is the reason we are segregating each years data and saving it in separate files(QVD) ad then calling all the data in main app.

can we automate this whole process and try to make it dynamic where date filters are for each year and data gets saved every year in separate files in QVD 

[WaltShpuntoff]

All you need to do is to set the values for vStart & vEnd relative to today's date. 

See https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFunc... for all of the functions available.

How about 

let vToday = Num(Today(1));

// for testing other dates, use make date for your test value

//let vToday = MakeDate(2023, 10, 31);

let vStart = YearStart(vToday, 1, 7);

let vEnd = YearEnd(vToday, 1, 7);

You may need to adjust the year, etc. Make sure you test this for different dates to make sure it behaves the way you expect.

-ws

[WaltShpuntoff]

All you need to do is to set the values for vStart & vEnd relative to today's date. 

See https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFunc... for all of the functions available.

How about 

let vToday = Num(Today(1));

// for testing other dates, use make date for your test value

//let vToday = MakeDate(2023, 10, 31);

let vStart = YearStart(vToday, 1, 7);

let vEnd = YearEnd(vToday, 1, 7);

You may need to adjust the year, etc. Make sure you test this for different dates to make sure it behaves the way you expect.

-ws

[ritumishra01]

let vcurrentyear = year(Today());
let vcurrentyear_1 = (year(today())+1);
let vcurrentdate = (today()); //10/31/2023
let vStartCheck = date#('07/01/$(vcurrentyear)','MM/DD/YYYY') ;//07/01/2023
let vEndCheck =date#('06/30/$(vcurrentyear_1)','MM/DD/YYYY');//06/30/2024

----
if(date($(vcurrentdate)) <= date($(vEndCheck))) then
 let vStart1 = date#('07/01/$(vcurrentyear)','MM/DD/YYYY')  ELSE

let vStart1 = date#('07/01/$(vcurrentyear_1)','MM/DD/YYYY')
;

Can you help with the syntax its giving error and also how can i write the query where start date and end date is dynamically changed every year 

[WaltShpuntoff]

I am not sure why you are continuing on this path and not using YearStart / YearEnd.

Dates are duals. Depending on the QS version, variables are not. For the most reliable method - always make sure your dates numbers.

Instead of 

let vStartCheck = date#('07/01/$(vcurrentyear)','MM/DD/YYYY') ;//07/01/2023

let vEndCheck =date#('06/30/$(vcurrentyear_1)','MM/DD/YYYY');//06/30/2024

Try

let vStartCheck = Num(MakeDate($(vcurrentyear), 7));

let vEndCheck =Num(MakeDate($(vcurrentyear_1),6, 30));

HTH

-ws

[ritumishra01]

Hey Walt, thanks for your response.

i am using the below query .and i could not achieve what is needed.

IF-ELSE is not working as expected.

what we are looking for is this that vstart should be '7/1/2023 00:00' and vENd should be '6/30/2024 23:5923:59';

and as soon as '6/30/2024 23:5923:59'; date reaches .

Vstart should become 7/1/2024 00:00'  and Vend should be  '6/30/2025 23:5923:59';.

Any help will be much apriciated .

let vcurrentyear = year(Today());
let vcurrentyear_1 = (year(today())+1);
let vcurrentyear_2 = (year(today())+2);
let vcurrentdate = date(today(),'MM/DD/YYYY');
let vStartC = num(date#(MakeDate($(vcurrentyear), 07,01),'MM/DD/YYYY'));
let vEndC =num(date(MakeDate($(vcurrentyear_1),06, 30),'MM/DD/YYYY'));

IF(NUM($(vcurrentdate)) > NUM($(vStartC)))

THEN
let vStart = date#('07/01/$(vcurrentyear)','MM/DD/YYYY') ;
let vEnd = date#('06/30/$(vcurrentyear_1)','MM/DD/YYYY');

ELSE
let vStart = date#('07/01/$(vcurrentyear_1)','MM/DD/YYYY');
let vEnd = date#('06/30/$(vcurrentyear_2)','MM/DD/YYYY');

END IF
;