Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to filter the data load from a SQL server comparing against a field that uses the following date format example: '2018_February'.
Currently, I can filter using the where and like, as shown below:
[RefreshSRList]:
LOAD
[ID] AS [RefreshSRList.ID],
[RefreshType] AS [RefreshSRList.RefreshType],
[RefreshCycleName] AS [RefreshCycle-RefreshCycleName],
[SMTicket] AS [RefreshSRList.SMTicket],
[Processed];
SQL SELECT "ID",
"RefreshType",
"RefreshCycleName",
"SMTicket",
"Processed"
FROM "RefreshMon"."dbo"."RefreshSRList"
WHERE RefreshCycleName like '2018_February';
But rather than having to alter the '2018_February' every month, can I use SYSDATETIME or such to use the current month year into a format that will compare against the 'RefreshCycleName' field?
Thanks!
The command should be before the load statement and should be a LET rather than a SET:
Let vDate = Year(Now()) & '_' & Date(Now(), 'MMMM');
Before your load script:
Set vDate=Year(Now())&'_'&Date(Now(),'MMMM');
In your where clause:
WHERE RefreshCycleName like '$(vDate)';
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others
This does not seem to be working for my case. I do not get any errors when the data loads, but it does not pull any records with this. This may be an issue with me (new to SQL and all), where exactly do I need to place the 'Set vDate' line in the load script? Currently, I have tried a few different locations, but none have worked.
The command should be before the load statement and should be a LET rather than a SET:
Let vDate = Year(Now()) & '_' & Date(Now(), 'MMMM');
That was it, thank you both for the help!