Qlikview is taking date column as text from excel

[#1]

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

[sunny_talwar · Accepted solution]

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);

[#3]

Hi Sunny,

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

[#4]

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

[sunny_talwar]

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?

[m_woolf]

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