Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Create day and hour field

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?

4 Replies

Re: Create day and hour field

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;


talk is cheap, supply exceeds demand
Not applicable

Re: Create day and hour field

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!

Re: Create day and hour field

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Create day and hour field

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.

Community Browser