Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

eduardo_dimperio
Valued Contributor 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
MVP
MVP

Re: Convert to Date Format

Maybe

Date(Num#(DATA))

7 Replies
marcelviegas
Contributor II

Re: Convert to Date Format

insert the example

eduardo_dimperio
Valued Contributor 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;

MVP
MVP

Re: Convert to Date Format

Maybe

Date(Num#(DATA))

eduardo_dimperio
Valued Contributor II

Re: Convert to Date Format

Hi marcel,

code bellow

eduardo_dimperio
Valued Contributor II

Re: Convert to Date Format

Yeah!

But why this work and others not?

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().

eduardo_dimperio
Valued Contributor II

Re: Convert to Date Format

Stefan thanks for the explanation