Populate fields between a start time and end time

[rmistry07]

Hi,

I hope someone will be able to help me with this.

I have a small part of a dataset that is a timeline of hits on a different webpages on a site. See example below:

Activity	ViewingDate	User	Device	ViewingTime	TimeonPage	EndTime	PageRef	Page	PageLaunchDate	PageLaunchTime	deviceinuse	Browser	TotalViewers
1-7 PM	20201002	1	01	0753	0002	0754	00036	Microneedle Vaccine - English (UK)	20201001	1957	0033	6	5
1-7 PM	20201002	2	01	2216	0002	2217	00044	Microneedle vaccine - Welsh	20201001	1957	0033	6	2

I need to create a graph that shows the entire timeline of the day and TotalViewers, which can later be filtered by webpages and all the other variables in the table headers.

So I think I need to create a table like above but for each minute between the ViewingTime and EndTime, a new row is created that is identical to the row its derived from. Could someone explain how I would do this in the qlik data loader for every single possible row in the dataset?

I know this seems like a lot of work but i would appreciate any help  🙂

Thank You

[edwin]

are you saying you need to create a row for each minute between view time and end time?  so for 1st row, you want 

Time:
0753
0754

so it end time were 0755:

0753
0754
0755

and all other fields are exactly the same?

maybe you dont need to create the rows, you can create a bridge instead.  the concept will be like the following:

in your table, format it as hh:mm add the following: VIEW TIME & '|' & END TIME as %KEY

in your bridge you will have the following values:

%KEY	TIME
07:53|07:54	07:53
07:53|07:54	07:54

this way you can create a chart using TIME as your dimension and measure will be how you want in in a regular chart.  the diff is that this allows you to plot across all time.  and if you have huge data you dont need to use up resource for duplicate info you dont need.

obviously, this is assuming you wanted to plot something by time of day and not a date time basis

data:
load time(ViewingTime,'hh:mm') as ViewingTime, 
time(EndTime,'hh:mm') as EndTime,
time(ViewingTime,'hh:mm') & '|' & time(EndTime,'hh:mm') as %Key inline [
ViewingTime,EndTime	
07:54, 07:55
22:16, 22:17
09:22, 09:28
];

NoConcatenate
Times:
load time(time('00:00','hh:mm') + (RowNo() -1)/(24*60),'hh:mm') as time
AutoGenerate (60*24);

NoConcatenate
tmpBridge:
load Distinct
%Key,
EndTime,
ViewingTime
resident
data
;


inner join (tmpBridge)
load time
Resident
Times;


NoConcatenate
bridge:
load
%Key,
time
Resident
tmpBridge
where time >= ViewingTime
and time <=EndTime
;

drop table tmpBridge;

 

[Kushal_Chawda]

@rmistry07  try below

LOAD
    Activity,
    ViewingDate,
    User,
    Device,
    ViewingTime,
    time(time#(ViewingTime,'hhmm'))+MakeTime(0,IterNo()-1) as ViewingTimeNew,
    TimeonPage,
    EndTime,
    PageRef,
    Page,
    PageLaunchDate,
    PageLaunchTime,
    deviceinuse,
    Browser,
    TotalViewers
FROM [lib://Web]
(html, utf8, embedded labels, table is @1)
while IterNo()<=Minute(time(Time#(EndTime,'hhmm'))- time(time#(ViewingTime,'hhmm')))+1;

[rmistry07]

Thank you edwin, this is a much better way of doing what I need. I appreciate the help 🙂 

[rmistry07]

Thank you Kush. I think this would work if my dataset was smaller.

But its taken over 4 hours and still not loaded, which is because of my methodology.