.NET sdk Filter Datetime column

[alessandrovernile]

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:

[Øystein_Kolsrud · Accepted solution]

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);

[alessandrovernile]

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

var fineField = app.GetField("Fine");                   
//var competenzaField = app.GetField("Gestione di Competenza");                   
//var fineField = app.GetField("Fine");                   


if (!string.IsNullOrWhiteSpace(dataInizioFilter)) 
{
    try
    {
        DateTime dataInizio = DateTime.ParseExact(dataInizioFilter, "dd/MM/yyyy HH:mm", CultureInfo.InvariantCulture);
        DateTime dataFine = DateTime.ParseExact(dataFineFilter, "dd/MM/yyyy HH:mm", CultureInfo.InvariantCulture);
        //inizioField.Select(dataInizioFilter);
        // Applica il filtro sulla colonna "Fine"
        //string dateFilterExpression = $"=[Fine] >= '{dataInizio:yyyy-MM-dd}' AND [Fine] <= '{dataFine:yyyy-MM-dd}'";
        string dateFilterExpression = $"=[Fine] >= '{dataInizio:dd/MM/yyyy HH:mm}' AND [Fine] <= '{dataFine:dd/MM/yyyy HH:mm}'";
        fineField.Select(dateFilterExpression);
        //fineField.SelectValues(dataInizio, dataFine);
    }
    catch
    {
        return req.CreateResponse(HttpStatusCode.BadRequest, "Data Inizio non valida");
    }
    
}

[Øystein_Kolsrud]

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

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);