Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

Date format

Hi

can anyone help me to convert text date formate to date format in qlikview

1 Solution

Accepted Solutions

Re: Date format

With the function date#() you could convert text to dates. More could you find here: The Date Function.

- Marcus

10 Replies

Re: Date format

With the function date#() you could convert text to dates. More could you find here: The Date Function.

- Marcus

Not applicable

Re: Date format

Thanks Marcus.

But i am using the same formula but it not loading in qlikview it is showing some field not found error.

I also wanted to share that Qlikview but not getting the share option here.

Re: Date format

It's probably only a typo and qv is case-sensitive by fields. You could share scripts and screenshots by simply copying it here. To add files you need to open the extend editor:

- Marcus

sangeethag
New Contributor III

Re: Date format

Hi,

Another way to convert text to date

Syntax:

MakeDate(Year,Month,Day)

Ex:

Date(MakeDate(Right(Date,4),Mid(Date,4,2),Left(Date,2)),'DD.MM.YYYY')

Re: Date format

I think this related to: Problem in date function whereby a document is the wrong place to ask a question. To the question itself you need to apply the converting after the crosstable-load in a further load then the crosstable will return only strings.

- Marcus

Not applicable

Re: Date format

Here find the attached QV File

Thanks & Regards,

Trapti Singh Gaharwar <trapti.gaharwar@acclimited.com>

Price Management

ACC Limited

Regional Office West

ACC Thane Complex

LBS Marg, Thane 400604

Maharashtra, India

Mobile: +917709043889

trapti.gaharwar@acclimited.com

On Tue, Feb 23, 2016 at 3:58 PM, Sangeetha G <qcwebmaster@qlikview.com>

Re: Date format

You need a further load to perform the converting:

TableTransformingPerCrosstable:

CROSSTABLE(Date, GP, 4)

LOAD AO,

     Date,

    Cnty,

  County,

  [01.02.2016],

  [02.02.2016]

  FROM Billing_022016.xlsx (ooxml, embedded labels, table is Sheet1);


Final:

load *, Date#(Date,'DD.MM.YYYY') as Date1 Resident TableTransformingPerCrosstable;

drop tables TableTransformingPerCrosstable;


- Marcus

Not applicable

Re: Date format

Thank you so much this worked for my billing sheet.

Billing:

Directory;

CROSSTABLE(Date, GP, 3)

LOAD AO,

//date#(Date,'DD.MM.YYYY') as date,

Cnty,

County,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

FROM Billing_022016.xlsx (ooxml, embedded labels, table is Sheet1);

Final:

load *, Date#(Date,'DD.MM.YYYY') as Date1 Resident Billing;

drop tables Billing;

Here is my second sheet of discount.

Kindly help me to clear my one more doubt as well.

For this sheet again i have to use this

Final:

load *, Date#(Date,'DD.MM.YYYY') as Date1 Resident PE;

drop tables PE;

date converting script which have been used in billing table already or it

will work for both the sheet

PE:

Directory;

CROSSTABLE(Date, PE, 3)

LOAD SU_new,

Cnty,

//County,

Cluster,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

,

FROM PE_022016.xlsx (ooxml, embedded labels, table is );

On Tue, Feb 23, 2016 at 5:30 PM, Marcus Sommer <qcwebmaster@qlikview.com>

Re: Date format

You need to do it for each table again.

- Marcus

Community Browser