Date Format - Timestamp

[#1]

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.

[#2]

Hi,

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

-Jai

[#3]

Hi,

Thanks.

It converts dates to value.

It doesn't solve my problem.

Something else...

[Anonymous]

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

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

[Miguel_Angel_Baeyens]

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

[ToniKautto]

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.

[#7]

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.

[Miguel_Angel_Baeyens]

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

[#9]

Dear Miguel,

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

[Miguel_Angel_Baeyens]

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