Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
Highlighted
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
Highlighted

Re: Qlikview is taking date column as text from excel

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
Highlighted

Re: Qlikview is taking date column as text from excel

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

Highlighted
Not applicable

Re: Qlikview is taking date column as text from excel

Hi Sunny,

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

Highlighted
Not applicable

Re: Qlikview is taking date column as text from excel

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

Highlighted

Re: Qlikview is taking date column as text from excel

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?

Honored Contributor II

Re: Qlikview is taking date column as text from excel

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