Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI,
I have data like each customer log data with start time and end time.I want to show the data for each minute wise like
how many customers are logged at that point of minute,likewise I need to for each day for each minute how many customers are logged in.
I tried to create Time table and want to join with customer log table, but how to generate minute wise customer data with my source data .
for example: CustA has logged from 10/07/2020 10:30:00 to 10/07/2020 10:35:00 --this is one record in my source
now this should show like 5 records
custA 10:30:00
custA 10:31:00
custA 10:32:00
custA 10:33:00
custA 10:34:00
custA 10:35:00
Any one can help here to get my desired output.
can you share also the expected output ?
try below. Note that as per your excel data. I have used different approach to convert your dates in timestamp format. You may need to use other methods as per your actual data. May be using timestamp#
Also be careful with this as it will create a lot of records based on your inputs.
Data:
LOAD
Customer,
Channel,
timestamp(left("Start Date Time",5)&Mid("Start Date Time",6,Len("Start Date Time"))) as "Start Date Time",
timestamp(left("End Date Time",5)&Mid("End Date Time",6,Len("End Date Time"))) as "End Date Time"
FROM [lib://Data/TimeSeries.xlsx]
(ooxml, embedded labels, table is Sheet1);
Final:
Load *,
time((num("Start Date Time")*24*60 -1 + IterNo())/1440) as Time
Resident Data
While num("Start Date Time")*24*60 + IterNo()-1<=num("End Date Time")*24*60;
Drop Table Data;