How to Restrict the data for current year data only.

[sanjaya9203]

Hi Friends,

I need your help on the below topics.

I am trying to extract the data from JSON data source where I have different dates such as sDate
tradeDate, sDateActual, updatedAt and trying o restrict on the settle date field by comparing with today() the where clause is currently use as  Where match(Status,'OPEN', 'Open', 'SETTLED', 'Settled') and sDate<=Today();. However it is working and takes 2hours to load the data and nearly 7M records fetching. Now I need to restrict the same as I don't need that much of data for my apps. I tried for Year(updatedAt) =Year(Today()), it not fetching any records.

your suggestion/solution is highly appreciated.

[Daniel_Castella]

Hi @sanjaya9203 

Without seeing any data sample or example it is a bit difficult to analyze, but it is possible that the field updatedAt is being considered a string. Then, you will need something like this to make it a date and extract the year. 

Year(Date#(updatedAt))=Year(Today())

With Date# and Date functions you can adjust the date format as you prefer. But to just extract the year with this should be enough. If the issue still persists, I can check deeper if you provide me with some of the data.

Kind Regards

Daniel

[Nagaraju_KCS]

When Qlik loads data from JSON, fields like updatedAt are often initially treated as strings, not numeric date values.

Using Date#
You must use the Date#() function to force Qlik to interpret the string as a date before you can use the Year() function on it.