Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Newbie question I'm afraid (yes, I'll search the community, but was hoping someone might take pity)
I have a file with a bunch of dates in this format;
December 1 2015 08:56 PM
I want to;
Add counts on hourly timebuckets
Add counts on days of the week, Mon,Tues etc
Add counts on days of the month 1,2,3
Any help appreciated;
Thanks for looking.
1) read your timestamps in, set the format code correctly or use Timestamp#() to interprete the timestamps
2) Create additional fields using date and time functions
3) create a chart with one of the new fields as dimension and count(FIELD) as expression
SET TimestampFormat = 'MMMM D YYYY hh:mm TT';
LOAD TimestampField,
Hour(TimestampField) as Hour,
Weekday(TimestampField) as WeekDay,
Day(TimestampField) as Day
FROM ...;
Now create e.g. a chart with dimension Hour and as expression
=Count(Hour)
Hi,
it looks like your dates are stored as strings and not timestamps. An easy way to tell is to preview the data and see if the values appear right-justified (numbers and timestamps) or left-justified (strings).
In case your dates are strings, you need to convert the string to the proper timestamp data type. Something like this should work:
timestamp#(MyDate, 'MMMM D YYYY hh:mm TT')
Then, you might want to format the result using a new format pattern:
timestamp(timestamp#(MyDate, 'MMMM D YYYY hh:mm TT'), 'new format patten')
Once this is done, you can use QlikView Date functions to calculate Year, Month, WeekDay, Hour, and whatever else you need.
If you'd like to learn QlikView development techniques methodically, starting from the basics and going into the most advanced topics, check out my new book QlikView Your Business.
cheers,
Oleg Troyansky
Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!
1) read your timestamps in, set the format code correctly or use Timestamp#() to interprete the timestamps
2) Create additional fields using date and time functions
3) create a chart with one of the new fields as dimension and count(FIELD) as expression
SET TimestampFormat = 'MMMM D YYYY hh:mm TT';
LOAD TimestampField,
Hour(TimestampField) as Hour,
Weekday(TimestampField) as WeekDay,
Day(TimestampField) as Day
FROM ...;
Now create e.g. a chart with dimension Hour and as expression
=Count(Hour)
if your field is dt
Set LongMonthNames = 'January;February;March;April;May;June;July;August;September;October;November;December';
load
dt, t,
WeekDay(t) as weekday,
Day(t) as day,
floor(frac(t),1/24)*24 as hour;
load dt, timestamp(Timestamp#(dt, 'MMMM D YYYY hh:mm tt')) as t;
load * inline [
dt
December 1 2015 08:56 PM
December 1 2015 08:01 PM
December 1 2015 08:01 AM
December 1 2015 08:56 AM
December 1 2015 12:01 AM
];
Thanks guys for taking the time to reply. I think you all contributed to me getting the right answer.
SET TimestampFormat = 'MMMM D YYYY hh:mm TT';
LOAD timestamp#(@1, 'MMMM D YYYY hh:mm TT'),
Hour(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Hour,
Weekday(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as WeekDay,
Day(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Day,
Month(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Month,
Week(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Week,
Year(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Year
FROM [lib://qlikid_jpqlikqlik/demo.csv]
(txt, codepage is 1252, no labels, delimiter is ',', msq);
It works really well.
Next step is for me to work out how to have year+month, so Dec15 can be separated form Dec16.
I'll keep playing.
Thanks again.
Have a look at the HELP, date and time functions:
Date and time functions ‒ Qlik Sense
Search for MonthName() or look into MonthStart() and format the returned date using Date() function.
Thank You.