Between Start and end date in the Where condition while loading QVD and Loop through each FY year

[CK_WAKE]

Hi There,

Currently, I have an SQL query that extracts the snapshot of each financial period (01-07-2019 to 01-07-2023), using a date mapping table as a reference to determine the FY Year. Now, I have loaded the data into a QVD file, and I need to apply the same WHERE clause that was applicable in SQL. However, I am unsure how to achieve this in QVD. I would greatly appreciate your assistance with this matter.

Below is the current code.

/*============SQL Load==============*/
/* Date mapping table */
NoConcatenate
DATELIST:
LOAD 
Distinct "FISC_NAME" as [FY Period]
FROM [$(DATE.qvd](qvd)
WHERE FISC_NAM >= '2019' and FISC_NAM < Year(Today());
for _i = 0 to NoOfRows('DATELIST') -1 
LET vYear_Current = peek('FY Period', _i, 'DATELIST');

SUMMARY_ANALYSIS:
SQL
WITH DATA AS (
    SELECT ID,
        '$(vYear_Current)' as FY Period
    FROM TABLE
    WHERE TO_DATE('01' || '-' || '07' || '-' || '$(vYear_Current)', 'DD-MM-YYYY' ) BETWEEN START_DATE AND END_DATE
    )
    SELECT * FROM DATA;

SET vYear_Current =;
next _i;

exit script; 

/*=================QVD Load Assistance needeed here========================================*/
NoConcatenate
DATELIST:
LOAD 
Distinct "FISC_NAME" as [FY Period]
FROM [$(DATE.qvd](qvd)
WHERE FISC_NAME >= '2019' and FISC_NAME < Year(Today());
for _i = 0 to NoOfRows('DATELIST') -1 
LET vYear_Current = peek('FY Period', _i, 'DATELIST');

SUMMARY_ANALYSIS_QVD:
LOAD 
ID,
   '$(vYear_Current)' as FY Period
    FROM [$(TABLE.qvd](qvd)
    WHERE CLAUSE NEED HELP
   ;
   
SET vYear_Current =;
next _i;

exit script; 

[Nicolae_Alecu]

Hello @CK_WAKE ,

Before loading the qvd, you can make an autogenerated calendar based on  vYear_Current. 

And your clause should be: 
Where Exists (CalendarDate, QvdDate)

With this approach you will have an optimized load .

Best Regards,

[CK_WAKE]

I would appreciate it if I could receive an example of this, as I am new to Qlik.

[CK_WAKE]

Currently, there are numerous SQL queries utilising the above mentioned Date reference table. It would be helpful if I could simply incorporate the WHERE clause as mentioned earlier.

[Nicolae_Alecu · Accepted solution]

SQL Clause: 
WHERE TO_DATE('01' || '-' || '07' || '-' || '$(vYear_Current)', 'DD-MM-YYYY' ) BETWEEN START_DATE AND END_DATE

Qlik Clause: 
WHERE DATE#('01-'&'07-'&'$(vYear_Current)','DD-MM-YYYY') >= START_DATE AND 
DATE#('01-'&'07-'&'$(vYear_Current)','DD-MM-YYYY') <= END_DATE

 Or Define Reference date first 

Let vReferenceDate = DATE#('01-'&'07-'&'$(vYear_Current)','DD-MM-YYYY');

Load...
WHERE vReferenceDate >= START_DATE AND vReferenceDate <= END_DATE

Best Regards,

[CK_WAKE]

It worked, thanks for the help. Cheers.