Create day and hour field

[#1]

For my report I need to look at hours within a day. I want to create a field that has mon12:00, mon13:00, mon14:00 etc. for a whole week. Now I included this in my script:

Weekday(date)&' '&hour&':00' as DayHour,

This gives me what I need, only it sorts in the wrong way. The variable "hour" is loaded from database as an integer with values from 0 to 23. Now this needs to be formatted or primed in some way so that Qlikview sees it as hours in a day with sort order 0 - 23. Also the weekday is only sorting aphabetically.

How can this field be created so that it sorts properly?

[Gysbert_Wassenaar]

Try num(hour,'00') so the hour always has two digits

Days:

load * inline [

day

Mon

Tue

Wed

Thu

Fri

Sat

Sun];

join

Hours:

load num(rowno()-1,'00') &':00' as hour

AutoGenerate 24;

Result:

load day&hour as dayhour

Resident Days;

drop table Days;

[#3]

Hi Gysbert, thanks for the input! I tried it in my script, but don't get the right output. I deleted "Hours:" which blocked the script. Autogenerate has an issue with the 24, does that need brackets?

I don't see how this script ties into the field "hour" which is in my original database, in this script you are creating a new field called "hour" from scratch. Also, why are the day names loaded by inline load, they are already specified by the set statement and the date field in the script?

Can you explain what your script tries to do, I am rather new to the scripting so it is hard for me to understand what is going on.

Thanks for any further help, it is much appreciated!

[Gysbert_Wassenaar]

Just try: Weekday(date)&' '&num(hour,'00')&':00' as DayHour,

num(hour,'00') formats the single digit integer as a two digit number.

My script is just an example that generates 24 hours (00 to 23) and then outer joins it with a table of days. The set variable merely specifies which values should be used for day names. It's not a table.

[#5]

O I ran that in the script, and then the sorting problem is still there. When the DayHour field is created, Qlikview does not see it as a date, so it will only allow me to sort the day names alphabetically.