topic Re: How do we get count less than 5 days from current date in QlikView

How do we get count less than 5 days from current date

saivina2920 — Sat, 16 Nov 2024 04:06:42 GMT

How do we get count less than 5 days from current date.

I have "column A" which contains varchar data type and i want to get count of less than 5 days from current date.

What expression we can use..?

Re: How do we get count less than 5 days from current date

Vegar — Wed, 20 Mar 2019 05:33:43 GMT

Try an expression like this one using SET analysis.

Count( {< Date={">=$(=Date(max(Date)-5))"} >}[column A])

Re: How do we get count less than 5 days from current date

saivina2920 — Wed, 20 Mar 2019 05:47:15 GMT

I have tested as you mentioned the expression.

But, it is not working.

Pls. find the sample attached file which i want to get count 1). < 10 Days and 2). 10 to 20 Days count.

Re: How do we get count less than 5 days from current date

MindaugasBacius — Wed, 20 Mar 2019 05:58:43 GMT

There is only one date that is >=Date-5:

I have also attached qvw file.

Re: How do we get count less than 5 days from current date

Vegar — Wed, 20 Mar 2019 08:00:02 GMT

You had a bit scattered data, not five days in a row. I made som adjustments to prove the consept.

LOAD * ,  
chr( mod(rowno(),4)+65) as [category a],
Date(floor(Date#(EmpJoinDate,'DDMMMYYYY:hh:mm:ss')),'DD-MM-YYYY') as  NewDate
;
LOAD * Inline [
EmpJoinDate
04APR2019:09:55:01
26MAR2019:10:56:34
28MAR2019:12:37:40
03MAR2019:13:03:52
02APR2019:09:28:32
26MAR2019:09:31:16
31MAR2019:09:32:06
01APR2019:09:33:33
13MAR2019:14:03:30
05APR2019:09:40:45
26MAR2019:09:44:51
03APR2019:21:16:43
03MAR2019:08:29:20
02MAR2019:09:57:55
10MAR2019:00:31:05
26MAR2019:09:59:55
04APR2019:22:57:52
29MAR2019:07:25:20
20MAR2019:22:59:19
];

This allow me to have this default view usin the expresion in the blue text box.

Re: How do we get count less than 5 days from current date

saivina2920 — Wed, 20 Mar 2019 08:39:39 GMT

Thank you very much..
currently I'm using SQL Server as Back End and my Query is SQL SELECT Statement not LOAD from EXCEL and other source.
How do i convert my varchar value to date in my script page.
below is my script.
SQL SELECT [EmpJoinDate],
Date(Date#(EmpJoinDate,'DDMMMYYYY:hh:mm:ss'),'DD-MM-YYYY') as EmpJoinDate ==> Will this Work in SQL SELECT Statement.....???

Re: How do we get count less than 5 days from current date

Vegar — Wed, 20 Mar 2019 09:42:03 GMT

You are right, you can not use Qlik syntax inside a SQL query. Try to do your date manipulation inside a preceding load.

Table:
LOAD
Date(Floor(Date#(EmpJoinDate,'DDMMMYYYY:hh:mm:ss')),'DD-MM-YYYY') as EmpJoinDate
;
SQL SELECT
   [EmpJoinDate]
FROM 
   DB.dbo.TABLE
;

Please note that I've added a Floor() in the script, this is to store the date integer and not the timestamp value as the numeric part of EmpJoinDate. (An date in QlikView is a dual value containing both a numeric value and a text representation )

Re: How do we get count less than 5 days from current date

saivina2920 — Wed, 20 Mar 2019 10:02:42 GMT

Error : OLEDB read failed
SQL SELECT [EmpJoinDate]

Also LOAD EmpJoinDate will not be loading in my listbox.

but, same SQL Select EmpJoinDate will be loading in my ListBox.

Why LOAD EmpJoinDate will not be loading in my listbox..?

Re: How do we get count less than 5 days from current date

Vegar — Wed, 20 Mar 2019 10:14:32 GMT

You should probably skip the brackets []

Try

SQL SELECT 
EmpJoinDate
FROM 
DB.dbo.Table
;

Re: How do we get count less than 5 days from current date

saivina2920 — Wed, 20 Mar 2019 10:46:21 GMT

No..Still blank list even if i remove brackets []

below my scripts.

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=**********;Initial Catalog=*****;Data Source=TEST;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=********;Use Encryption for Data=False;Tag with column collation when possible=False];

LOAD *,

Date(Floor(EmpJoinDate), 'DD.MM.YYYY') AS EmpJoinDate;

SQL SELECT EmpJoinDate
FROM
DB.dbo.EmpTable";

Re: How do we get count less than 5 days from current date

Vegar — Wed, 20 Mar 2019 10:47:17 GMT

Try to get the correct connect syntax by using the connect / select syntax
by using the connect wizard inside the script editor.

Re: How do we get count less than 5 days from current date

saivina2920 — Wed, 20 Mar 2019 11:26:17 GMT

I deleted all the scripts and re-connect it again. Script Generated Successfully with LOAD and SQL Select statement.
I just changed the field like "Date(Floor(Date#(EmpJoinDate,'DDMMMYYYY:hh:mm:ss')),'DD-MM-YYYY') as EmpJoinDate" in LOAD part and Re-Load it again.
But, Again it is loading blank for EmpJoinDate.
Not sure, how i could proceed further...?

Re: How do we get count less than 5 days from current date

Vegar — Wed, 20 Mar 2019 11:46:32 GMT

Maybe the assumed format is not correct?
Could you post a picture of an list box with EmpJoinDate if you load that field straight from the database without any manipulation? You could also pay attention to the alignment of the values in the list box, is the EmpJoinDate left or right aligned?

SQL SELECT
EmpJoinDate
FROM
DB.dbo.Table
;

Re: How do we get count less than 5 days from current date

saivina2920 — Wed, 20 Mar 2019 12:29:38 GMT

The tricky is, the same will be loading successfully in the LOAD(without converting date) .
But, while converting the same field in the LOAD, it won't be list.
What is the magic behind this....?

Same data and Screenshot attached.

Re: How do we get count less than 5 days from current date

saivina2920 — Wed, 20 Mar 2019 13:15:09 GMT

Some rows are blank in the "EmpJoinDate" Column.
Because of this...????

Re: How do we get count less than 5 days from current date

Vegar — Fri, 22 Mar 2019 05:36:57 GMT

By looking at the picture I notice that Your date format is not DDMMMYYYY:hh:mm:ss but MM/DD/YYYY hh.mm.ss
Try changing your expression inside the LOAD to:
Date(floor(Date#(EmpJoinDate,'MM/DD/YYYY hh.mm.ss'))) as EmpJoinDate

Re: How do we get count less than 5 days from current date

saivina2920 — Sun, 24 Mar 2019 00:42:26 GMT

Excellent...You Rock...