Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Not applicable

Try like below:

Load

     Floor(DT_CREAT) AS Date_created

     Count(DT_CREAT)

From source

group by

     Floor(DT_CREAT)

;

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

;

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

erivera10
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 ;

Not applicable

Thank you...

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

erivera10
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;

erivera10
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;

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

erivera10
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.