Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Converting string to Date 'DDMMYY' format

Hi,

I have loaded a series of dates below.

LOAD

    Sales Agent,

    "42158",

    "42165",

    "42172",

    "42179",

    "42186",

    "42193",

    "42200",

    "42207",

    "42214"

How can I convert them into DDMMYY format? I've seen a few suggestions online but wasn't able to get it working... thanks for your help!

Tags (2)
23 Replies
MVP
MVP

Re: Converting string to Date 'DDMMYY' format

Are these column names? Are you cross-table loading them?

If they are column names and you not cross table loading them, and if you know the min and max dates, you could create a mapping table, and rename the fields using the mapping table. For example:

MapNames:

Mapping LOAD

     RecNo() + 42157 As DateNum,

     Date(RecNo() + 42157) As Date

Autogenerate (42214 - 42158 + 1);

Rename Fields using MapNames;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Converting string to Date 'DDMMYY' format

Hi Jonathan, sorry I should have mentioned that the dates are crosstabled.

Crosstable(Date,Sales)

LOAD

    Sales Agent,

    "42158",

    "42165",

    "42172",

    "42179",

    "42186",

    "42193",

    "42200",

    "42207",

    "42214"

FROM [lib://Allen/time-series....xlsx]

Not applicable

Re: Converting string to Date 'DDMMYY' format

juz use Date(fieldnme,'DDMMYYYY')

Not applicable

Re: Converting string to Date 'DDMMYY' format

That doesn't work... Sales Agent is not a date field...

MVP
MVP

Re: Converting string to Date 'DDMMYY' format

Like this then:

T_Data:

Crosstable(Date,Sales)

LOAD

    Sales Agent,

    "42158",

    "42165",

    "42172",

    "42179",

    "42186",

    "42193",

    "42200",

    "42207",

    "42214"

FROM [lib://Allen/time-series....xlsx]

Data:

NoConcatenate

LOAD [Sales Agent],

  Date(Date) As Date,

  Sales

Resident T_Data;

DROP TABLE T_Data;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Converting string to Date 'DDMMYY' format

If Date(Date) is not giving the format you desire, then use something like Date(Date, 'DDMMYY') to get the format you require.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Converting string to Date 'DDMMYY' format

Hmm.. I've tried your suggestion, but I still get dates in format like 42158, for example. Any suggestion?

jyothish8807
Honored Contributor II

Re: Converting string to Date 'DDMMYY' format

HI Allen,

Try like this:

date(trim(replace([Sales Agent],'"','')),'MMDDYY') as New field

Regards

KC

Best Regards,
KC
ganeshreddy
Contributor III

Re: Converting string to Date 'DDMMYY' format

Hi Jyotish and Allen,

Allen saying that Sales Agent is not the Date field, i advice him to replace it by Date field on your code. I hope it helps him.

Cheers,

Ganesh.

Community Browser