Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

Convert to Date Format

Hi everyone,

I have this situation, a crosstable from a excel file with a Date Field (in number format) and i tried everything that i know to convert that number to a date format.

To Data = 43017

INPUT:

date(DATA)

date#( DATA, 'M/D/YY')

NUM(DATA)

TEXT(DATA)

TIMESTAMP(DATA,'DD/MM/YYYY')

OUTPUT:

null

43017

null

43017

null

Tags (1)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Convert to Date Format

Maybe

Date(Num#(DATA))

View solution in original post

7 Replies
Highlighted
Creator II
Creator II

Re: Convert to Date Format

insert the example

Highlighted
Specialist II
Specialist II

Re: Convert to Date Format

Just to compliment, i'll post my code bellow:

TMP:

Crosstable (Data, NAME_SYSTEM)

LOAD

*

FROM [lib://VisitaMI (bi_bi.desenvolvimento)/CONTROLE - AGENDAMENTO -2016-2017-2018 V*.xlsx]

(ooxml, embedded labels, table is Geral);

Noconcatenate

AUX:

Load

    Unidades AS UNIDADES,

    OID_SYSTEM,

    Data AS DATA,

    Funcionários AS FUNCIONARIO,

'Agendado' AS AGENDAMENTO

resident TMP

WHERE Data>NUM(TODAY()-5);

drop table TMP;

Highlighted
MVP
MVP

Re: Convert to Date Format

Maybe

Date(Num#(DATA))

View solution in original post

Highlighted
Specialist II
Specialist II

Re: Convert to Date Format

Hi marcel,

code bellow

Highlighted
Specialist II
Specialist II

Re: Convert to Date Format

Yeah!

But why this work and others not?

Highlighted
MVP
MVP

Re: Convert to Date Format

The CROSSTABLE LOAD prefix will return the column headers as field values in your new DATA column, but unfortunately as pure text values.

Pure text values can't be formatted using formatting functions Date() or Timestamp(), or Num().

Pure text values can be interpreted using interpretation functions, but Date#(DATA, 'M/D/YY') is not using the correct format code for your integer number. Instead, use Num#() to interpret the text as number.

A correctly interpreted number can then be formatted as Date using Date() or Timestamp().

Highlighted
Specialist II
Specialist II

Re: Convert to Date Format

Stefan thanks for the explanation