Converting string to Date 'DDMMYY' format

[#1]

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!

[jagan]

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]

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:

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