Set Values as columnName, Create table with loop

Wed, 25 Nov 2020 16:16:04 GMT

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.ID	Report.StartTime	Report.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

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.

Time	Report1	Report2	Report3	Report4	Report5
1:00	0	0	0	0	0
2:00	0	0	0	1	0
3:00	0	0	0	1	1
4:00	0	1	0	1	1
...	..	..	..	..	..
7:00	0	1	0	1	1
8:00	1	1	0	1	1
...	..	..	..	..	..
16:00	0	1	1	0	0
17:00	0	0	1	0	0
...	..	..	..	..	..

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),

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

Re: Set Values as columnName, Create table with loop

Colin-Albert — Mon, 04 Apr 2016 15:37:46 GMT

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 ;

Re: Set Values as columnName, Create table with loop

Tue, 05 Apr 2016 09:36:05 GMT

That absolutely answered my questions.

Thank you, Colin Albert.

topic Re: Set Values as columnName, Create table with loop in QlikView

Set Values as columnName, Create table with loop

Re: Set Values as columnName, Create table with loop

Re: Set Values as columnName, Create table with loop