Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
CK_WAKE
Creator
Creator

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

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; 

 

 

1 Solution

Accepted Solutions
Nicolae_Alecu
Creator
Creator

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,

View solution in original post

5 Replies
Nicolae_Alecu
Creator
Creator

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
Creator
Creator
Author

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

CK_WAKE
Creator
Creator
Author

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
Creator
Creator

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
Creator
Creator
Author

It worked, thanks for the help. Cheers.