Showing Data from last 24hrs

[Anonymous]

I have data in a table that records how long it takes to run a report and if the report completed or failed.  It basically has report name, Status, start time and end time.  The Start Time is formatted as such:

START_TIME

29-AUG-18 05.12.41.382000000 PM

I just want to grab a count of reports that ran in the past 24hrs and their status.  Trying to show something like this:

Status	Count
COMPLETED	120
FAILED	5
RUNNING	30

How can I do that in qlikview?

I tried: =Count({<STATUS>,START_TIME >= {'>=$(=timestamp(now()-1))'}})

But I get no count numbers.  Any advice?

[marcus_sommer]

There are a few syntax-issues and therefore try it with this:

= Count({<STATUS =, START_TIME = {">=$(=now()-1)"}>} [report name])

Important is further that your START_TIME is really a timestamp.

- Marcus

[tulabandula]

As like above try below..

= Count({<

STATUS ={'*'}

, Date_field = {">=$(=today()-1)"}

>}ID)

Note: Make sure the date field format and validate with today or Now functions in the text box.

[Anonymous]

I am loading data from an Excel file.  Is there a way I can make sure the data in the START_TIME column is being loaded as a DATE format into QLIK when using the Data Load Editor?

Example:

LOAD

ReportName,

Status,

Start_Time,

End_Time

FROM [lib://qlik_RptData.xls (ad_JDoe)/qlik_RptData.xls]

(biff, embedded labels, table is [Export Worksheet$]);

Can I CAST/CONVERT that Start_Time into a DATE FORMAT (in case it's being read as text)?

If so, what would be that syntax considering the data is formatted as: 29-AUG-18 05.12.41.382000000 PM?

Thanks!

[marcus_sommer]

If it's really a string - you could just check it with num(Start_Time) which will return either NULL by a string or a float by a numeric content - you could convert and format it with something like:

timestamp(timestamp#('29-AUG-18 05.12.41.382000000 PM', 'DD-MMM-YY hh.mm.ss.fff TT'))

- Marcus

[Anonymous]

Unfortunately none of these suggestions worked...

I changed my connection to now connect directly to Prod DB (instead of reading from Excel file)

To make this simpler, how can I just get a count of the various statuses?  I don't care if it's last 24 hrs. I just want a total count of the diff statuses.

COMPLETED: 320

FAILED:12

RUNNING: 56

etc..

[marcus_sommer]

Regardless from the used source you need to know exactly how the data are stored/transferred which might be different to how they are displayed. If your applied formatstring differs in any way from the real fieldvalues a conversion will fail - I think you should give it a few more (systematically) trials because you will need these measures again and again.

Get the Dates Right

Why don’t my dates work?

On Format Codes for Numbers and Dates

If your dates and times work you could just use Status as dimension in an object to get with = Count([report name]) your results. Without such a dimension you could put a Status query into the expression with something like: = Count({<STATUS = {'COMPLETED'} >} [report name]).

- Marcus