how to use count and cast functions in qlikview

[#1]

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)

[#2]

Try like below:

Load

     Floor(DT_CREAT) AS Date_created

     Count(DT_CREAT)

From source

group by

     Floor(DT_CREAT)

;

[#3]

some more method:

Load

     Date_created,

     COunt(DT_CREAT)

Group by

     Date_created

;

Load

     Floor(DT_CREAT) AS Date_created

    DT_CREAT

From source

;

[#4]

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

[erivera10]

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 ;

[#6]

Thank you...

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

[erivera10]

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;

[erivera10]

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;

[#9]

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

[erivera10]

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.