topic Re: Populate fields between a start time and end time in App Development

Populate fields between a start time and end time

rmistry07 — Sat, 16 Nov 2024 18:00:19 GMT

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

Re: Populate fields between a start time and end time

edwin — Wed, 07 Oct 2020 20:18:59 GMT

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;

Re: Populate fields between a start time and end time

Kushal_Chawda — Wed, 07 Oct 2020 21:39:24 GMT

@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;

Re: Populate fields between a start time and end time

rmistry07 — Thu, 08 Oct 2020 17:33:46 GMT

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

Re: Populate fields between a start time and end time

rmistry07 — Thu, 08 Oct 2020 17:39:11 GMT

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.