Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III

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

View solution in original post

6 Replies
Gysbert_Wassenaar
Partner - Champion III

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
Not applicable
Author

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)

Gysbert_Wassenaar
Partner - Champion III

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
Not applicable
Author

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

Gysbert_Wassenaar
Partner - Champion III

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
Not applicable
Author

It's working well! I had filled the Dimension fields with the expression but not the field expression.. It works! Thank you a lot!