Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ryanreid
New Contributor

Filter data load from SQL server

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!

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Filter data load from SQL server

The command should be before the load statement and should be a LET rather than a SET:

Let vDate = Year(Now()) & '_' & Date(Now(), 'MMMM');



5 Replies
jwjackso
Contributor III

Re: Filter data load from SQL server

Before your load script:

Set vDate=Year(Now())&'_'&Date(Now(),'MMMM');

In your where clause:

WHERE RefreshCycleName like '$(vDate)';

pablolabbe
Valued Contributor II

Re: Filter data load from SQL server

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

ryanreid
New Contributor

Re: Filter data load from SQL server

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.

MVP
MVP

Re: Filter data load from SQL server

The command should be before the load statement and should be a LET rather than a SET:

Let vDate = Year(Now()) & '_' & Date(Now(), 'MMMM');



ryanreid
New Contributor

Re: Filter data load from SQL server

That was it, thank you both for the help!