topic Qlikview is taking date column as text from excel in QlikView

Qlikview is taking date column as text from excel

Thu, 11 Feb 2016 19:30:57 GMT

Hi,

My excel file has 2 date columns(ShipDate and PurchaseDate) with timestamp. I am loading the excel data into a qvw file and applying PurchaseDate-ShipDate as ResponseDate. Now ResponseDate column is not giving me the difference for all rows. It is giving me the difference in 1 row only, in rest 2 rows it is giving me -(Hyphen).

Please help me on this. I want the difference in minutes.(Attached qvw file and excel file both)

Below is my date:

Customer	Sales Order ID	ShipDate	Product	Sales	Quantity	PurchaseDate
Tyrone, Sanz	SO64706	4/10/08 10:21 AM	All-Purpose Bike Stand	159.00	1	03/10/2008 17:21:00 PM
Jesse, Carter	SO64983	14/06/2013 11:23:05 AM	All-Purpose Bike Stand	159.00	1	13/06/2013 11:23:05 PM
Alexandra, Jenkins	SO65103	03/08/2013 21:12:00 PM	All-Purpose Bike Stand	159.00	1	03/08/2013 19:11:00 PM

Re: Qlikview is taking date column as text from excel

sunny_talwar — Thu, 11 Feb 2016 21:34:19 GMT

I think your timestamps are not read correctly within QlikView. My feeling is that they might need to be fixed within the Excel, but this script seems to work:

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

LOAD *,

Interval(ShipDate-PurchaseDate, 'D hh:mm') as DateDifference;

LOAD Customer,

[Sales Order ID],

TimeStamp(Alt(Num(ShipDate), Left(ShipDate, Len(ShipDate)-3))) as ShipDate,

ShipDate as ShipDate1,

Product,

Sales,

Quantity,

PurchaseDate as PurchaseDate1,

TimeStamp(Alt(Num(PurchaseDate), Left(PurchaseDate, Len(PurchaseDate)-3))) as PurchaseDate

FROM

[Test Data.xlsx]

(ooxml, embedded labels, table is Sheet2);

Re: Qlikview is taking date column as text from excel

Sat, 13 Feb 2016 02:41:56 GMT

Hi Sunny,

Thanks for your prompt response. It is working perfectly..

Re: Qlikview is taking date column as text from excel

Wed, 17 Feb 2016 18:04:47 GMT

Hi Sunny,

I have applied your solution and got the desired result, but facing one issue.

1) If both dates are same but timstamp is different then i am getting difference in negative. For Exp:

Customer	Sales Order ID	ShipDate	Product	Sales	Quantity	PurchaseDate
Tyrone, Sanz	SO64706	03/10/08 19:21 PM	All-Purpose Bike Stand	159.00	1	03/10/2008 17:21:00 PM

2) If ShipDate's Day is in double digit like 15/10/08 19:21 PM and PurchasDate's day is in single digit like 3/10/2008 17:21:00 PM then difference is in negative.

Customer	Sales Order ID	ShipDate	Product	Sales	Quantity	PurchaseDate
Tyrone, Sanz	SO64706	15/10/08 10:21 AM	All-Purpose Bike Stand	159.00	1	3/10/2008 17:21:00 PM

Please suggest.

Thanks

Push

Re: Qlikview is taking date column as text from excel

sunny_talwar — Wed, 17 Feb 2016 18:45:54 GMT

I think you need to know before hand what is your date field format here. Do you have a list of format the date can come in?

Re: Qlikview is taking date column as text from excel

m_woolf — Wed, 17 Feb 2016 18:46:08 GMT

Your Timestamps are misbehaving in Excel:

The Timestamp of: 4/10/2008 10:21:00 AM is Apr 10, 2008 at 10:21 AM - this is a valid timestamp in ExcelThe Timestamp of: 14/06/2013 11:23:05 AM is NOT a valid timestamp in Excel - the month can't be 14 - it is being read in as a string

The Timestamp of: 03/08/2013 21:12:00 PM - this is NOT a valid timestamp in Excel - when you use PM, the hour can't be 21 - it is being read in as a string