Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

how to use count and cast functions in qlikview

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)

11 Replies
Highlighted
Not applicable

Try like below:

Load

     Floor(DT_CREAT) AS Date_created

     Count(DT_CREAT)

From source

group by

     Floor(DT_CREAT)

;

Highlighted
Not applicable

some more method:

Load

     Date_created,

     COunt(DT_CREAT)

Group by

     Date_created

;

Load

     Floor(DT_CREAT) AS Date_created

    DT_CREAT

From source

;

Highlighted
Not applicable

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

Highlighted
Creator
Creator

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 ;

Highlighted
Not applicable

Thank you...

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

Highlighted
Creator
Creator

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;

Highlighted
Creator
Creator

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;

Highlighted
Not applicable

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

Highlighted
Creator
Creator

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.