topic Re: .NET sdk Filter Datetime column in Integration, Extension & APIs

.NET sdk Filter Datetime column

alessandrovernile — Tue, 09 Jul 2024 17:04:08 GMT

I need to filter a table on Qlik Cloud and then retrieve the data using GetHyperCubeDataAsync. The filter works correctly when I apply it to a string column, but it doesn't work when I try to apply it to the 'Inizio' column, which is a date. What am I doing wrong? Thanks

IQcsLocation location = QcsLocation.FromUri(qlikApiUrl); location.AsApiKey(qlikApiKey); using (IApp app = location.App(qlikAppId)) { //string identifFilter = "2-7-1-12-30"; DateTime dateTime = new DateTime(2023, 6, 13, 10, 40, 42, DateTimeKind.Utc); //var identifField = app.GetField("Identificativo"); // Applica i filtri sulle date var inizioField = app.GetField("Inizio"); var fineField = app.GetField("Fine"); // Converti le date nel formato richiesto DateTime dataInizio = DateTime.ParseExact(dataInizioFilter, "M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture); //DateTime dataFine = DateTime.ParseExact(dataFineFilter, "M/d/yyyy", CultureInfo.InvariantCulture); var inizioFieldValues = new List<FieldValue> { new FieldValue{ IsNumeric = true, Number = dateTime.ToOADate() } }; inizioField.SelectValues(inizioFieldValues.ToArray(), true); }

This is my qlik table:

Re: .NET sdk Filter Datetime column

Øystein_Kolsrud — Wed, 10 Jul 2024 07:28:29 GMT

Matching on float point values is always tricky. Are you sure there is not a millisecond part to those timestamp that you miss? The string formatting of timestamps often hides that part. And also, I'm not sure if the C# "ToOADate" produces the number in the format that Qlik uses, but perhaps you've already verified that.

You could potentially do a match based on the string representation of the timestamp. You already seem to have the "dataInzioFilter" available. With that you should be able to do something like this:

inzioField.Select(dataInzioFilter);

Re: .NET sdk Filter Datetime column

alessandrovernile — Mon, 15 Jul 2024 09:48:16 GMT

ok thanks,
How do I filter the field within a date range instead? I tried this way but it doesn't work:

Re: .NET sdk Filter Datetime column

Øystein_Kolsrud — Mon, 29 Jul 2024 12:10:49 GMT

If you want to do that type of a little more advanced selections, then you'll need to use a list object. That's how you do filtering in data using that magnifying glass in the client. This thread touches on how to do this for dates:

https://community.qlik.com/t5/App-Development/How-to-select-a-date-range-in-Qlik-Sense-without-using/td-p/1253520

I think the exact format to use for that filter depends on the default date and time format settings in your app, this is an example for how to select all timestamps for a particular date:

>=7/29/2024 12:00:00 AM<7/30/2024 12:00:00 AM

The .NET SDK has the convenience class "AppField" for doing operations on fields. With that class can do something like this to use the above filter:

var theFilter = ">=4/13/2023 12:00:00 AM<4/14/2023 12:00:00 AM";
var aF = app.GetAppField("LocalTime");
var path = aF.GetAllListObjectPagers().Single().Path;
aF.BeginSelections(new []{path});
aF.SearchListObjectFor(path, theFilter);
aF.AcceptListObjectSearch(path, true);
aF.EndSelections(true);