topic Re: Wildcard for date in QlikView

Wildcard for date

EDehzn — Mon, 08 Nov 2021 08:36:02 GMT

Hi,

I am trying to to a count ref. no. by date where the full date field is like 2021-10-05 13:45:21. My expression used is currently

count(if (WildMatch(wkflow_StartDt_AMWWF ,('2021-10-05*')),[Reference No] ))

However, the results returns multiple dates.

How can the count be specific based on date?

Re: Wildcard for date

Andrei_Cusnir — Mon, 08 Nov 2021 13:34:49 GMT

Hello,

If my understanding is correct, you want to count all the fields where date is "2021-10-05" regardless of the time. In this case you can use set analysis. For example:

This is my test dataset:
As you can see there are 5 records on the date 2021-10-05 and 5 records with the date 2021-10-06 and the time is different in all the dates.
I have created an KPI chart with the Measure expression: "Count({<Date={"2021-10-05*"}>}Value)".
This expression is using set analysis to count all the values in Value field, where date is "2021-10-05" and regardless of the time.
Here is the result:
As you can see, it has counted 5 records, which is correct

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in detail by providing more information, otherwise if this answer has helped you resolve the issue, please mark it as solution to help other community members find it as well!

Re: Wildcard for date

EDehzn — Tue, 09 Nov 2021 01:30:37 GMT

hi Andrei,

Yes that is what i have been trying to do. However, I have also tried using Count({<Date={"2021-10-05*"}>}Value) and it still returns count for other dates e.g. 2021-10-03, 2021-10-04 & 2021-10-06

Re: Wildcard for date

Andrei_Cusnir — Tue, 09 Nov 2021 09:21:23 GMT

Hello,

I just have noticed that my reproduction was in Qlik Sense, while you have posted this under QlikView. Therefore, I retried this with a new dataset:

As you can see I have here 5 dates "22021-10-05", 5 dates "2021-10-06" and 1 date "2021-10-07".

I have created an Text object with the expression "=Count({<Date={"2021-10-05*"}>}Value)" and the result is still 5, which is accurate.

On your side, however it is not working this way and this seems very strange indeed. Perhaps you can share with us a sample app and I will try to reproduce the issue on my side. I have a feeling that, this is either an issue with the dataset or an issue with the application itself. Because as you can see from my previous response and from the new reproduction in QlikView, on my demo test, it is working as expected. Please avoid sharing any sensitive information, I would recommend creating a new sample application with fake data and try to reproduce the issue there with the set analysis as you have mentioned. If the issue is still present in the new sample app, then you can share it here and we will take a look. Otherwise, if the issue is not present anymore with the sample data in the new app, then it means that you are probably on a good path to troubleshoot the issue.

Re: Wildcard for date

EDehzn — Tue, 23 Nov 2021 06:18:45 GMT

Hi Andrei,

It seems it was an issue during data loading where the field need to be declared as date. Then, I am able to create the results as showed by you.