Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Values as columnName, Create table with loop

Hi there,
I'm still new to QlikView and confused by my current problem.

I've got a simple table filled with Report-IDs and when they started and ended:

Report.IDReport.StartTimeReport.EndTime
Report18:008:03
Report24:0016:59
Report314:0017:44
Report42:009:06
Report53:0010:17

My Goal is to create a table that shows in which hours every report was active (for Report1 from 8:00-8:03 it's only the eighth hour of the day). In the script.

TimeReport1Report2Report3Report4Report5
1:0000000
2:0000010
3:0000011
4:0001011
.............
7:0001011
8:0011011
.............
16:0001100
17:0000100
.............


My current approach is to simply add the 24 hours as columns (instead of rows) to the Report.table and fill the rows via If-Selection:

Report:

LOAD @1 as Report.ID,

          Time(@7) as Report.StartTime,

          Time(@8) as Report.EndTime,

          IF ((Report lies within hour 1), 1, 0) as HourOne,

          IF ((Report lies within hour 2), 1, 0) as HourTwo,

          IF ((Report lies within hour 3), 1, 0) as HourThree,

          ...

That works... but it's rather time-consuming.


First Question:

Is it possible to add those 24 new Columns in a Loop? Something like:

FOR a=1 TO 24

    IF ((Report lies within hour $(a)), 1, 0) as Hour & $(a),

NEXT

All my tries failed.


Second question:

What if I really wanted to do it like in the table above? With all the Reports as Columns and one row for each Hour?


(Third question:

This may be absolutely basic, but if I wanted to add those new columns (IF ((@7 <= 1 AND @8 >= 2)) as HourOne,...) how can I address the columns? @7 and @8 don't seem to work.

LOAD     Report.ID,

              IF ((Report.StartTime <= 1 AND Report.EndTime >= 2)) as HourOne

Resident Report;

)


If those are absolutly trivial problems you can of course just give the keyword I obviously need for further research. Maybe I am just too slow on the uptake.

Thank you in advance








1 Solution

Accepted Solutions
Colin-Albert

Try this ...

Data:
load
Report,
time(StartTime) as StartTime,
time(EndTime) as EndTime
inline
[ Report, StartTime, EndTime
Report1, 8:00, 8:03
Report2, 4:00, 16:59
Report3, 14:00, 17:44
Report4, 2:00, 9:06
Report5, 3:00, 10:17
] ;


// loop through hours and flag if report is active
for h = 0 to 23
Active:
load
  Report,
  StartTime,
  EndTime,
  time(time#($(h) , 'hh')) as hour,
  if($(h) >= hour(StartTime), if( $(h) <= hour(EndTime), 1)) as active
resident Data ;
next h ;

drop table Data ;

View solution in original post

2 Replies
Colin-Albert

Try this ...

Data:
load
Report,
time(StartTime) as StartTime,
time(EndTime) as EndTime
inline
[ Report, StartTime, EndTime
Report1, 8:00, 8:03
Report2, 4:00, 16:59
Report3, 14:00, 17:44
Report4, 2:00, 9:06
Report5, 3:00, 10:17
] ;


// loop through hours and flag if report is active
for h = 0 to 23
Active:
load
  Report,
  StartTime,
  EndTime,
  time(time#($(h) , 'hh')) as hour,
  if($(h) >= hour(StartTime), if( $(h) <= hour(EndTime), 1)) as active
resident Data ;
next h ;

drop table Data ;

Not applicable
Author

That absolutely answered my questions.

Thank you, Colin Albert.