Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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!

23 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

Temp:

Crosstable(Date,Sales)

LOAD

    Sales Agent,

    "42158",

    "42165",

    "42172",

    "42179",

    "42186",

    "42193",

    "42200",

    "42207",

    "42214"

FROM DataSource;

Data:

NoConcatenate

LOAD [Sales Agent],

  Date(Num(Date), 'DDMMYY') As Date,

  Sales

Resident Temp;

DROP TABLE Temp;

Hope this helps you.

Regards,

Jagan.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I think Caio's solution is correct but just to summarize what is happening here.

1. You can't apply the date() formatting in the CrossTable load. It has to be done in a subsequent load.

2. CrossTable makes all attribute values text().

3. You can't directly format a text value with date(). First you have to convert it to numeric, like with a num#() function.

  Date(num#(Date), 'DDMMYY')


-Rob

http://masterssummit.com

http://robwunderlich.com

MarcoWedel

test xlsx

MarcoWedel

Hi,

try to avoid hard coded column names (dates) in the script and use * instead.

one solution could be:

QlikCommunity_Thread_167921_Pic1.JPG

tabSales:

Crosstable(DateTemp,Sales)

LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/796668-169791/QlikCommunity_Thread_167921.xl...] (ooxml, embedded labels, table is Tabelle1);

Left Join (tabSales)

LOAD Distinct

  DateTemp,

  Date#(DateTemp,'DD.MM.YYYY') as Date

Resident tabSales;

DROP Field DateTemp;

hope this helps

regards

Marco