Count occurences for each day with timestamp

[#1]

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

[Gysbert_Wassenaar]

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)

[#3]

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]

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;

[#5]

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 · Accepted solution]

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.

[#7]

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