Issue with Time(hh:mm) Master calendar

[Anil_Babu_Samineni]

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.

[Anil_Babu_Samineni]

If you notice in tabular format? There is misplaced values from Excel 12:10:00 AM to Qlik Sense 12:09:59 AM. Why this is happened? Due to that it consider as over problem in chart? It should one value only. there could be some misreads the Qlik while loading data from connection. Can you have a look which i reply to MK.

[marcus_sommer]

I think this is related to Rounding Errors and you need to round the master timetable and the date/time-fields from your fact-table to ensure that both are the same.

- Marcus

[Anil_Babu_Samineni]

Bookmarked / Noted !!! But, While i am trying to use same the values are not returning anything. The moment when i generate ID's and call that in to table also not getting anything. Seems, I missed something - And in that link i read all comments as well.

This is what i tried so far? Time( Floor( TimeStamp#( A),1/24/60/60 )) as A

[marcus_sommer]

There could be another potential difference between Excel and Sense related to the formatting in excel. I had recently the case in which a timestamp from a csv was shown in 'DD.MM:YYYY hh:mm:ss' but the real formatting by looking with notepad++ was 'YYYY/MM/DD hh:mm:ss'.

In short the value in excel might be different to how excel displayed it and therefore go to the cell, copy the content to another cell and applying another formattting with and without a rounding-approach.

- Marcus

[Anil_Babu_Samineni]

I appreciate your long conversation. But, The problem here is we are getting data flow (Automation) from Sources (Which are different server) and For that they are using SQL Queries and Ctrl + M. This time, I don't think whether they will change. Is there any short cut. If this resolve the problem I think mrkachhiaimpsolution may works. First troubleshoot needed. I am silly, Why Date() function not working here

[MarcoWedel]

Hi,

I guess the issue indeed lies with your excel source.

Adding decimal places to your timestamp A reveals that it's off by a fraction of a second.

One solution to correct this behaviour might be to round to the next multible of 10 minutes:

table1:
LOAD Timestamp(A,'DD/MM/YYYY hh:mm:ss TT') as A,
     Timestamp#(Timestamp(Round(A,'00:10:00'),'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss TT') as DateTime,
     AV,
     HW,
     HC,
     BA
FROM [lib://SampleDataSet] (ooxml, embedded labels, table is Sheet1);

tabCalendar:   
LOAD Timestamp#(Timestamp(MinDate+(IterNo()-1)*'00:10:00','DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss TT') as DateTime
While MinDate+(IterNo()-1)*'00:10:00' < MaxDate;  
LOAD DayStart(Min(A)) as MinDate,  
     DayEnd(Max(A)) as MaxDate  
Resident table1;

hope this helps

regards

Marco

[Anil_Babu_Samineni]

This deserve and get relief for me. But, My real question is - If you look attachment there are null values first thing is i want to remove nulls (I used this object Extension - https://github.com/NielsLindberg/Qliksense.Extension.amWaterfall)

From there, If i have more dates nulls getting produced for that, Same chart i want to compare Today's and Last Date in same chart. In between they want vertical line. Can you check the original file if you have time.

Requirement - If 2 days data come thru same chart we need like. Let's assume if i have data from 10:00 AM - 3:00 PM for Today's data and same from yesterday data like 5:00 PM - 11:00 PM. I want to show only them. That means X-Axis need to be 10 - 3 and it should start from 4'o clock it self (In our case, Data should start from 5 PM not from 12:00 AM again). Continuation need to be show.

Does it make sense?

[dwforest]

the Date/Timepstamp functions in Qlik rely on the format set when the data is loaded and processed. If loading different formats change the format appropriately so that Qlik recognizes it as a date, otherwise it will be a string (that to you looks like a date).

If you have the option to output plain csv, it may solve the issue of Excel formatting the data for display vs what is actually stored.

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY HH:mm:ss';

[Anil_Babu_Samineni · Accepted solution]

Implemented, Thanks for your all support

[RUNIL]

Hi Anil,

How to include the time in master calendar? 

The data is not iterating  time with below 2 scripts. second logic is giving an error. Can you please suggest how to solve?

1) LOAD
Timestamp#(Timestamp(MinDate,'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss TT') as DateTime
While MinDate+(IterNo()-1) <= MaxDate;

LOAD DayStart(Min(A)) as MinDate,
DayEnd(Max(A)) as MaxDate
Resident table1;

2) TempDate:
LOAD
Floor(Min(TimeStamp#(KeyDate, 'MM/DD/YYYY hh:mm:ss'))) AS MinDate,
Floor(Max(TimeStamp#(KeyDate, 'MM/DD/YYYY hh:mm:ss'))) AS MaxDate
FROM [$(EXECUTIVE_QVDAGGR)Link_Final.qvd](qvd);

Load
DayStart(TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1))) AS TempDate,
//Use this one as date
TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1)) AS AddedTimeStamp
AUTOGENERATE 86399 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));