topic Re: Date Format - Timestamp in QlikView

Date Format - Timestamp

Mon, 13 Jun 2011 05:39:16 GMT

Dear Friends,

I am using QV file linked with Excel workbook having one of the field as Date.

Some dates in this column are in DD/MM/YYYY format while few are DD/MM/YYYY hh:mm:ss format.

In QlikView file, in one of the table I am having this field.

In this field, date is displayed based on condition as it is linked with Variable Date selection.

This selected date appears in DD/MM/YYYY format.

Query:

When I convert All Dates in Excel file in DD/MM/YYYY format, it works properly.

If there are mixed formats, it doesn't work.

In Script, I tried function

DATE([date],'DD/MM/YYYY') as Date

Still it didn't work.

Please contribute your knowledge to resolve this issue.

Thanks.

Date Format - Timestamp

Mon, 13 Jun 2011 06:00:43 GMT

Hi,

You can try like Date#(Field,'DD/MM/YYYY') AS Field

-Jai

Date Format - Timestamp

Mon, 13 Jun 2011 06:07:35 GMT

Hi,

Thanks.

It converts dates to value.

It doesn't solve my problem.

Something else...

Re: Date Format - Timestamp

Anonymous — Mon, 13 Jun 2011 06:59:16 GMT

Just try the below one...

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

//SET DateFormat = 'M/D/YYYY' => Default one

SET DateFormat='DD/MM/YYYY'; => after modification

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Load Date(Field) as Date_Field

From

.............

Re: Date Format - Timestamp

Miguel_Angel_Baeyens — Mon, 13 Jun 2011 07:32:21 GMT

Hello Ravi,

What exactly are you expecting? Since dates in QlikView are numeric formats, and one unit is one complete day, you may find dates with decimals, (for those values where you have DD/MM/YYYY h:mm:ss in excel). So depending on what you want to see, you may need to modify the load script so the dates are all converted to the day, without decimals (not taking into account hours or minutes)

Table:
LOAD Date(Date#(Floor(ExcelDateField))) AS Date,
     *
FROM Excel.xls ....

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Date Format - Timestamp

ToniKautto — Mon, 13 Jun 2011 09:19:36 GMT

Depending on what your actual data in excel is, you might need to use Date#() or Date() or even a combination of both in order to get your QV table correctly formatted.

Not sure what you mean by mixed value, is the excel column containing both textual dates and numeric date representation? If so, then you could use IsNum() in combination with an IF statement to identify which method to use in your load statement in order to make the format correct.

Please provide a sample QVW and Excel to demonstrate you issue in order to make evaluation of your case a bit easier.

Re: Date Format - Timestamp

Mon, 13 Jun 2011 10:02:44 GMT

Dear Friends,

Thanks for responding.

Let me clarify my query.

Please find attachment.

In "Selection date", I need to manually mention required date in DD/MM/YYYY format.

DateQryReply is also in same format CAPTURED in SCRIPT.

But in Excel, some dates are in DD/MM/YYYY format while some are in DD/MM/YYYY hh:mm:ss format.

Rest two tables show data of those date mentioned in Selection date location.

If data is in DD/MM/YYYY format, it appears correctly. Not otherwise.

Re: Date Format - Timestamp

Miguel_Angel_Baeyens — Mon, 13 Jun 2011 10:12:50 GMT

Hi,

Again, if you mean you have in your DateQryReply several values for the same date, that means that data from excel file contains decimals (hours and minutes) and although the representation of date is correct, there are several different numeric values for them.

Try in your listbox using

Date(Floor(DateQryReply))

Instead of the field alone. Is this the expected result? If so, you can use the code above in the load script to load dates (only dates, not hours nor minutes) when pulling from your excel file.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Date Format - Timestamp

Mon, 13 Jun 2011 11:20:23 GMT

Dear Miguel,

After using this in script, nothing is getting displayed in DateQryReply field.

Re: Date Format - Timestamp

Miguel_Angel_Baeyens — Mon, 13 Jun 2011 11:45:08 GMT

Hi,

The code

Date(Floor(DateQryReply))

Is to be used in the lisbox instead of the field itself. For that, go to the listbox properties, General tab, expand the Field list and at the bottom select <Expression> and copy and paste the code.

In the script, the code should be something like

Table:
LOAD Date(Date#(Floor(ExcelDateField))) AS Date,
     *
FROM Excel.xls ....

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Date Format - Timestamp

Mon, 13 Jun 2011 12:02:29 GMT

Hi,

Sorry, even above code didn't succeed.

Re: Date Format - Timestamp

Miguel_Angel_Baeyens — Mon, 13 Jun 2011 12:31:48 GMT

Hi,

Then use the following

Table:
LOAD
     Date(Floor(Date)) AS DateQryReply,
...
FROM File.xls

This is working fine here, but it will depend on your excel raw data.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Date Format - Timestamp

Tue, 14 Jun 2011 06:32:52 GMT

Hi,

Sorry,

even this code gives blanks. Neither dates nor values.

Re: Date Format - Timestamp

Miguel_Angel_Baeyens — Tue, 14 Jun 2011 07:33:18 GMT

Hello,

Then that makes me assume that your excel file has some issue when being read by QlikView. Can you post some sample rows so we can check it? I tried yesterday with a simple excel file and different date formats and the load was fine.

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Date Format - Timestamp

Wed, 15 Jun 2011 15:54:57 GMT

Ravi,

Try it directly in the script. What Miguel has suggested should work.

Date(Floor(ExcelDateField)) as DateQryReply

Nimish

Re: Date Format - Timestamp

Wed, 15 Jun 2011 15:55:51 GMT

Ravi,

Try it directly in the script. What Miguel has suggested should work.

Date(Floor(ExcelDateField)) as DateQryReply

Nimish