topic Re: how to use count and cast functions in qlikview in QlikView

how to use count and cast functions in qlikview

Wed, 20 Jan 2016 14:28:58 GMT

Namaste to all

could you please tell me, how to write the below sql query in qlikview

SELECT CAST(DT_CREAT AS DATE) , Count(*) from Autopallet_registration GROUP BY CAST(DT_CREAT AS DATE)

Re: how to use count and cast functions in qlikview

Wed, 20 Jan 2016 17:16:35 GMT

Try like below:

Load

Floor(DT_CREAT) AS Date_created

Count(DT_CREAT)

From source

group by

Floor(DT_CREAT)

;

Re: how to use count and cast functions in qlikview

Wed, 20 Jan 2016 17:17:43 GMT

some more method:

Load

Date_created,

COunt(DT_CREAT)

Group by

Date_created

;

Load

Floor(DT_CREAT) AS Date_created

DT_CREAT

From source

;

Re: how to use count and cast functions in qlikview

Thu, 21 Jan 2016 12:52:34 GMT

Hi dathu

Thanks for the response, but i couldnt get the result from the floor function I used 'DayName' Function and i got the result.

Thank you bhai@dattu.qv@dattu.qv

Re: how to use count and cast functions in qlikview

erivera10 — Thu, 21 Jan 2016 17:28:50 GMT

Hi,

If you are connecting to query the database, you need something like this:

Floor() rounds x down to the nearest multiple of step [+ offset]. The default value of offset is 0. Compare with the ceil function, which rounds input numbers up. Syntax: Floor(x[, step[, offset]])

floor:

Select SQL

Floor(DT_CREAT) as Date_created,

DT_CREAT

From Autopallet_registration ;

Count:

count(DT_CREAT) as count_created,

DT_CREAT

From Autopallet_registration ;

Re: how to use count and cast functions in qlikview

Fri, 22 Jan 2016 10:37:26 GMT

Thank you...

could you please tell me, how to use count function while using group by

Re: how to use count and cast functions in qlikview

erivera10 — Fri, 22 Jan 2016 15:24:41 GMT

Hi again,

When you have two fields do not need a GROUP BY, you served for 3 or more fields, but if you want to use would be.

Count:

Load count (DT_CREAT) as count_created,

DT_CREAT

from [Autopallet_registration](qvd)

GROUP BY DT_CREAT;

Another example would be:

Count:

Load ID,

Name ,

DT_CREAT,

count (DT_CREAT) as count_created

From [../qlik/qvd/Autopallet_registration.qvd](qvd)

Group BY ID, Name, DT_CREAT;

Re: how to use count and cast functions in qlikview

erivera10 — Fri, 22 Jan 2016 15:28:59 GMT

If you extract of the database, the example would be like this:

Count:

Select SQL ID,

Name ,

DT_CREAT,

count (DT_CREAT) as count_created

From Auto Pallet Registration

Group by ID, Name, DT_CREAT;

Re: how to use count and cast functions in qlikview

Fri, 22 Jan 2016 16:17:31 GMT

Thank you sir, for the quick response.

Now, I clearly understood, using of count.

But, i have a problem because of the time stamp in the database i am using.

2015-03-12 16:51:40.000

2015-03-12 16:54:00.000

2015-03-12 16:57:24.000

2015-03-12 17:02:00.000

2015-03-12 17:10:08.000

2015-03-12 17:10:52.000

2015-03-12 17:15:56.000

Task:

my aim is to "count the number of records per date".

so i have converted the "datetime" column to date and want to count the number of records per date. by grouping the records on same date.

I hope , I tried to explain my problem clearly.

Please see the code that i have wrote by using "Resident " keyword.

DATA1:

LOAD

"ID_ORDER",

DayName("DT_CREAT");

SQL SELECT *

FROM FDB.dbo."AUTOPALLET_REGISTRATION";

DATA2:

LOAD

"ID_ORDER",

Count(DayName(DT_CREAT)) as datecount

Resident Ganesh

Group By ID_ORDER;

Again. thank you for the quick response sir

Re: how to use count and cast functions in qlikview

erivera10 — Fri, 22 Jan 2016 17:33:23 GMT

In that case you need another field to group, example:

Count:

Select SQL LongDayNames(DT_CREAT) as Name_day,

DT_CREAT,

count (DT_CREAT) as count_created

From Auto Pallet Registration

Group by DT_CREAT;

Results:

Name_day, DT_CREAT, count (DT_CREAT) as count_created

thursday, 2015-03-12 17:02:00.000, 1

thursday, 2015-03-12 16:51:40.000, 2

thursday, 2015-03-12 16:54:00.000, 3

thursday, 2015-03-12 16:57:24.000, 4

monday, 2015-30-11 17:02:00.000, 1

monday, 2015-30-11 18:02:00.000, 2

monday, 2015-30-11 20:02:00.000, 3

Example New table box

thursday, 4

monday, 3

Or try a master calendar, it may be the best solution, depending on how much data you use.

Re: how to use count and cast functions in qlikview

Mon, 25 Jan 2016 16:27:07 GMT

sorry for the late reply. I will not have access to my VPN in the weekend, so, i couldnt work with the code,. that you gave .

I dont know, i am unable to get the result with the code that you gave.. I am getting the error as shown below, when i use the code that you gave.

May i ask you, how can we count the DT_CREAT, as i told u earlier, it has time stamp, so we cant group that column since, it qill have different value for each column depending on the time?

below is the error i get

but i got the required result with the below code, by counting the other column and grouping ti

DayName(DT_CREAT) , //AS Date_Created,

Count(ID_PLANT) AS Count1

Group By

DayName(DT_CREAT);

select * from Autopallet_registration

but, here i cam counting the other column, but i would like to count using the DT_CREAT column, is it possible

thanks

Re: how to use count and cast functions in qlikview

erivera10 — Mon, 25 Jan 2016 17:12:09 GMT

It is a source error, check that you have correctly configured the ODBC or OLE DB connection, make the connection test, if the connection works, try to bring a clean table, without modifications, or try to improve DBeaver Query works. Make sure you have a connection, existing table, and other commands.