Discussion Board for collaboration on QlikView Scripting.
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)
Try like below:
Floor(DT_CREAT) AS Date_created
some more method:
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 firstname.lastname@example.org@dattu.qv
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(DT_CREAT) as Date_created,
From Autopallet_registration ;
count(DT_CREAT) as count_created,
could you please tell me, how to use count function while using group by
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.
Load count (DT_CREAT) as count_created,
GROUP BY DT_CREAT;
Another example would be:
count (DT_CREAT) as count_created
Group BY ID, Name, DT_CREAT;
If you extract of the database, the example would be like this:
Select SQL ID,
From Auto Pallet Registration
Group by ID, Name, DT_CREAT;
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.
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.
SQL SELECT *
Count(DayName(DT_CREAT)) as datecount
Group By ID_ORDER;
Again. thank you for the quick response sir
In that case you need another field to group, example:
Select SQL LongDayNames(DT_CREAT) as Name_day,
Group by DT_CREAT;
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
Or try a master calendar, it may be the best solution, depending on how much data you use.