help needed - App not summing up

[#1]

Hi All,

Any one help me to explain why below behaviour?

Why my Date list box showing 2 records for same day?

If load via Inline it is summing all records where date is same, but from XLS not summing up why?

Thanks.

[geert_gelade]

Strange, I think it has something to do with the formatting of the dates in your xls-file. When I update the date-column in xls (just retyping the dates), the result in qlikview is ok (so same dates are combined).

[#3]

Hi,

Thanks for reply.

If I am loading data from database from 2 tables where as both have Date fields, How we can load as same Date format from 2 different tables (make sure same date format)?

Thanks.

[martinpohl · Accepted solution]

Look at the field Date, change the format to timestamp.

This date has two timestamp. although it is the same Date, Qlikview handle it as two dataset.

load

date(floor(Date)) as date

from your data

Regards

[m_woolf]

In your Excel workbook, Rows 6 and 7 have dates. Rows 8-12 have timestamps.

[JonnyPoole]

Your excel has some datetimes in it . Just strip the times out so that each day is recognized as the same value instead of multiple values (with multiple times). Use floor() as follows:

tab1:

  LOAD   Date(floor(Date)) as Date,

     Id,

     Id1Count,

     ID2,

     Id2Count

FROM

[sample.xls]

(biff, embedded labels, table is Sheet1$);

[#7]

Thanks to all.

I have to use Date(floor(Date)) as Date.

Any document which will explain all these kind of Key words?

Thanks.

[martinpohl]

look at the help

[JonnyPoole]

Good question.  The help files are extensive with many samples but a shortened list i think is what you are after. Probably a list of common date handling techniques .

Here are a few. You'll find more searching for 'dates' and filtering on 'documents'.

http://community.qlik.com/docs/DOC-2811

http://community.qlik.com/docs/DOC-3102

http://community.qlik.com/docs/DOC-4247

http://community.qlik.com/docs/DOC-1208

Couple things for this example:

1. If qlik recognizes the source values as dates or timestamps (dates with exact times), it will assign a numeric value to them. A whole number denotes a day. The decimal portion denotes a fraction of a day or a time.

2. Your list box is showing the source values as dates, but since there are multiple times on the date, you get a few values.

3. This is very common. Quick solution is to round the date number down effectively stripping out any record of the time . You can use floor() to do this.  Date() just makes sure it will be formatted as a date in the UI.