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:
|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 🙂
are you saying you need to create a row for each minute between view time and end time? so for 1st row, you want
so it end time were 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:
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;
@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;