
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count occurences for each day with timestamp
Hello,
in my project, i have a Table Twitter with: Timestamp Row and tweet(text) row.
I would like to draw a histogram in which On the X axis, i can see the date for exemple the last 6months and on the Y axis, the counting of tweets for each days.
However, my data is a timestamp and i am a little beginner in QlikView.. Could you help me to begin this task please?
Thanks a lot
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can try count(MyTime) as expression. That should display all dates and the count for each day.
But count({<MyDate={'>=$(=addmonths(max(MyDate),-6))'}>}MyTime) should work. See attached example. Just make sure you use the exact case-senstive field names. A field named MyDate is not the same as the field named Mydate.
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It's easiest if you create a date field and a time field in the script from the timestamp.
load
tweettext,
frac(TimeStamp) as MyTime,
floor(TimeStamp) as MyDate
from ...somewhere...;
You can then use MyDate as dimension and as expression something like this:
count({<MyDate={'>=$(=addmonths(max(MyDate),-6))'}>}MyTime)
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok i understand, but how and where should i put the load... script ?
Here is my connexion script:
ODBC CONNECT TO Hive;
SQL SELECT name, tag, time, tweet FROM HIVE."default".tableName;
And that is all (maybe i had to precise i am using Hive and Hadoop)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can use a preceding load to create the new fields:
ODBC CONNECT TO Hive;
MyTable:
LOAD name, tag, tweet, frac(time) as MyTime, floor(time) as MyDate;
SQL SELECT name, tag, time, tweet FROM HIVE."default".tableName;
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok, the load is working, i can have a list of Mydate under this format 23/05/2013 and MyTime 13:45:41.
I have tried to add a histogram with 2 dimensions: Mydate
and
=count({<MyDate={'>=$(=addmonths(max(MyDate),-6))'}>}MyTime)
No other option and it displays : no data to display.
Should i change another option?
Moreover, I don't really understand the count expression here.. but is there another expression (easier) which displays values since ever instead of last 6 months?
Thanks and sorry..

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can try count(MyTime) as expression. That should display all dates and the count for each day.
But count({<MyDate={'>=$(=addmonths(max(MyDate),-6))'}>}MyTime) should work. See attached example. Just make sure you use the exact case-senstive field names. A field named MyDate is not the same as the field named Mydate.
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It's working well! I had filled the Dimension fields with the expression but not the field expression.. It works! Thank you a lot!
