Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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)

Tags (1)
11 Replies
Not applicable

Re: how to use count and cast functions in qlikview

Try like below:

Load

     Floor(DT_CREAT) AS Date_created

     Count(DT_CREAT)

From source

group by

     Floor(DT_CREAT)

;

Not applicable

Re: how to use count and cast functions in qlikview

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

Re: how to use count and cast functions in qlikview

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
Contributor

Re: how to use count and cast functions in qlikview

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

Re: how to use count and cast functions in qlikview

Thank you...

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

erivera10
Contributor

Re: how to use count and cast functions in qlikview

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
Contributor

Re: how to use count and cast functions in qlikview

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

Re: how to use count and cast functions in qlikview

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
Contributor

Re: how to use count and cast functions in qlikview

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.

Community Browser