topic Re: Count Dates in QlikView

Count Dates

Sat, 12 Jan 2013 09:33:16 GMT

Dear,

I need to count dates expression if date formate complete entered.

order_date

2011-09-22 15:56:00

2011-09-23 21:01:00

2011-09-23 13:31:00

2011-09-22 19:36:00

2011-09-22 19:56:00

2011-09-21

2011-09-22 19:

2011-09-

Result is 5 which is highlighted in green.

Regards,

Zain

Re: Count Dates

Gysbert_Wassenaar — Sat, 12 Jan 2013 09:44:52 GMT

Try count(if(Len(Order_Date)=19,Order_Date)). This is not fool-proof, but may be enough for your purposes.

Re: Count Dates

Sat, 12 Jan 2013 13:40:36 GMT

Thanks for reply, you are right any idea if user change order of date, year, month, day and time for example

formate of date is 2011-09-22 15:56:00 but user enter 20-09-2011 15:56:00, so how we catch?

Regards,

Zain.

Re: Count Dates

nagaiank — Sat, 12 Jan 2013 14:00:00 GMT

Checking for length is one of the validations.

Validating pattern, validating data (e.g. hh part has number 00 thru 23, mm and ss parts have numbers 00 thru 59, month part has number 01 thru 12, date part has number 01 thru 28, 29, 30, or 31 depending on the month and year, etc.) are also to be performed.

If the data has 04-03-2012, do you interpret this as 3-Apr-2012 or 4-Mar-2012?

The checks that you need to add, depend on the source of data. If the source has bulit in several of these checks, you may skip some of the validations

You can build expressions for validations in the script, depending on what you want to implement.

Re: Count Dates

Gysbert_Wassenaar — Sat, 12 Jan 2013 14:41:09 GMT

This checks if a date can be made from the input with the specified date format:

count(if(isnum(date#(Order_Date,'YYYY-MM-DD hh:mm:ss')) ,Order_Date))

Unfortunately dates as 2011-09-25 19:84:12 are also accepted. So additional test is needed:

isnum(MakeTime(mid(Order_Date,12,2),mid(Order_Date,15,2),Mid(Order_Date,18,2)))

Bringing the total to:

count(if(isnum(date#(Order_Date,'YYYY-MM-DD hh:mm:ss')) and isnum(MakeTime(mid(Order_Date,12,2),mid(Order_Date,15,2),Mid(Order_Date,18,2))) ,Order_Date))