Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anil_Babu_Samineni

Issue with Time(hh:mm) Master calendar

Hello Strikers,

I need one favor on master calendar. I may request you to don't share links related stuff. Because, I've tried maximum. With dates there is no problem at all. But when we are doing with Time stamp making problem. Here is the small attachment and off course, Data also added.

What i needed - I want to generate data from 3:20 PM - 11:50 PM because we need to populate each day(Today only date, Future many dates) and that too needed 10 minutes interval for each like what i have in data.


Use Case - If you look chart there are only <=3:20 PM data but the chart is not looking as expected that's what we are going to show null values as well in future reference.

I just want to generate Time stamp and with zero data points. I have attached report which i need and data set too.

Mock-up attached.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
1 Solution

Accepted Solutions
Anil_Babu_Samineni
Author

Implemented, Thanks for your all support

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

19 Replies
MK_QSL
MVP
MVP

MyTable:

Load *, A as Orig;

Load

TimeStamp(TimeStamp#(A,'DD/M/YYYY h:mm:ss TT')) as A,

    AV, HW, HC, BA

Inline

[

A, AV, HW, HC, BA,

29/1/2018 12:00:00 AM, 1205.75, 1942.86, 2970.50, 1172.14

29/1/2018 12:10:00 AM, 1208.75, 1301.08, 1450.05, 1189.35

29/1/2018 12:20:00 AM, 1211.00, 1301.08, 1450.05, 1189.35

29/1/2018 12:30:00 AM, 1214.61, 1348.91, 1515.72, 1223.81

29/1/2018 12:40:00 AM, 1221.50, 2043.19, 3127.88, 1229.67

29/1/2018 12:50:00 AM, 1224.75, 1706.82, 2339.52, 1232.30

29/1/2018 1:00:00 AM, 1225.00, 1370.46, 1551.15, 1234.93

29/1/2018 1:10:00 AM, 1225.00, 1776.97, 1941.07, 1653.89

29/1/2018 1:20:00 AM, 1225.00, 1776.97, 1941.07, 1653.89

29/1/2018 1:30:00 AM, 1225.00, 1781.26, 1948.06, 1656.16

29/1/2018 1:40:00 AM, 1225.00, 1791.66, 1965.10, 1661.57

29/1/2018 1:50:00 AM, 1225.00, 1791.66, 1965.10, 1661.57

29/1/2018 2:00:00 AM, 1225.00, 1801.96, 1977.41, 1670.38

29/1/2018 2:10:00 AM, 1225.00, 1845.37, 2062.43, 1682.57

29/1/2018 2:20:00 AM, 1225.00, 1845.37, 2062.43, 1682.57

29/1/2018 2:30:00 AM, 1225.50, 1866.84, 2098.06, 1693.42

29/1/2018 2:40:00 AM, 1226.00, 1900.22, 2163.34, 1702.89

29/1/2018 2:50:00 AM, 1226.00, 1900.22, 2163.34, 1702.89

29/1/2018 3:00:00 AM, 1226.00, 1900.22, 2163.34, 1702.89

29/1/2018 3:10:00 AM, 1226.00, 1909.60, 2174.52, 1710.92

29/1/2018 3:20:00 AM, 1226.00, 1909.60, 2174.52, 1710.92

];


MinMaxDates: 

LOAD Floor(Min(TimeStamp#(A, 'DD/MM/YYYY h:mm:ss TT'))) as MinDate,  

     Floor(Max(TimeStamp#(A, 'DD/MM/YYYY h:mm:ss TT'))) as MaxDate 

RESIDENT MyTable; 

LET vMinDate = FieldValue('MinDate', 1); 

LET vMaxDate = FieldValue('MaxDate', 1);  

DROP TABLE MinMaxDates;  



CalendarTemp: 

Load

*,

Time(Frac(A)) as A_Time;

LOAD

    TimeStamp($(vMinDate) + (RecNo()/6/24) + (IterNo() -1)) as A

AUTOGENERATE 143

WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate)); 


Capture.JPG

marcus_sommer

I doubt that I would ever integrate a time-part into a master-calendar else I would (beside the master-calendar) use a master time-table by splitting the timestamp into a date and a time. This results in clean tables without any conflicts between them or by calculations and improved the performance: The Importance Of Being Distinct

- Marcus

Anil_Babu_Samineni
Author

Can you try with my file instead Inline in sense? That makes more clear

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni
Author

I already placed about performance because of each seconds will produce as we have seconds in Time stamp. But, They want to be forcibly? The reason behind if we have Ctrl+M schedule with all time-stamp would better if they make from ETL transaction with zero data points. But they are not doing that as already flowing thru to server.

To make it more clear, Whatever QVW file is there I've done the same with 60/60/24 as we have time sections and generating same using 60*60*24. The problem here is it is not considering the data as HH:MM format with the data.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MK_QSL
MVP
MVP

Sorry I can't... !! Sorry Again !

pradosh_thakur
Master II
Master II

Hi Anil,

Will this work ?

Don't suprass zero values and use ceil instead of floor in maxtimestamp?

MinMaxDates:
LOAD Floor(Min(TimeStamp(A, 'DD/MM/YYYY hh:mm:ss'))) AS MinDate, 
    Ceil(Max(TimeStamp(A, 'DD/MM/YYYY hh:mm:ss'))) AS MaxDate 
RESIDENT MyTable;

LET vMinDate = FieldValue('MinDate', 1); 
LET vMaxDate = FieldValue('MaxDate', 1);

DROP TABLE MinMaxDates;

Rem CalendarTemp: 
LOAD Date($(vMinDate) + IterNo() -1,'DD/MM/YYYY') AS AddedTimeStamp 
AUTOGENERATE 1 WHILE Date($(vMinDate) + IterNo() -1) <= $(vMaxDate);

Calendar: 
LOAD Timestamp($(vMinDate) + (RecNo()/60/24)) AS A
AUTOGENERATE 1440 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));

regards

Pradosh

Learning never stops.
Anil_Babu_Samineni
Author

I can understand, But i am not lazy to implement your suggestion. But, the problem here is not resolved. And for sure, with Inline memory it may works but not really sure the problem comes while load data. Because Excel Vs Qlik

                    EXCEL                        QLIK SENSE

Capture.PNGCapture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni
Author

Not really sure - Even i shared application with you. Why commands sharing script only. That would better help in case if you can apply same with app and share

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
pradosh_thakur
Master II
Master II

Here it is Anil..Hope this is what you are looking for.

Learning never stops.