Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Qlikview is taking date column as text from excel

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:

    

CustomerSales Order IDShipDateProductSalesQuantityPurchaseDate
Tyrone, SanzSO647064/10/08 10:21 AMAll-Purpose Bike Stand159.00103/10/2008 17:21:00 PM
Jesse, CarterSO6498314/06/2013 11:23:05 AMAll-Purpose Bike Stand159.00113/06/2013 11:23:05 PM
Alexandra, JenkinsSO6510303/08/2013 21:12:00 PMAll-Purpose Bike Stand159.00103/08/2013 19:11:00 PM
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

5 Replies
sunny_talwar

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

Not applicable
Author

Hi Sunny,

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

Not applicable
Author

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:

CustomerSales Order IDShipDateProductSalesQuantityPurchaseDate
Tyrone, SanzSO6470603/10/08 19:21 PMAll-Purpose Bike Stand159.00103/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.

CustomerSales Order IDShipDateProductSalesQuantityPurchaseDate
Tyrone, SanzSO6470615/10/08 10:21 AMAll-Purpose Bike Stand159.0013/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
Master II
Master II

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