Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
rmistry07
Contributor II
Contributor II

Populate fields between a start time and end time

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:

ActivityViewingDateUserDeviceViewingTime TimeonPageEndTimePageRefPagePageLaunchDatePageLaunchTimedeviceinuseBrowserTotalViewers
1-7 PM2020100210107530002075400036Microneedle Vaccine - English (UK)202010011957003365
1-7 PM2020100220122160002221700044Microneedle vaccine - Welsh202010011957003362

 

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

Labels (7)
4 Replies
edwin
Master
Master

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:

%KEYTIME
07:53|07:5407:53
07:53|07:5407: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;

 

 

 
 

Capture.PNG

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
Contributor II
Contributor II
Author

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

rmistry07
Contributor II
Contributor II
Author

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.