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!

[#2]

Hi Allen,

just one question, what is your data source. I tried it with an inline table and that works fine without brackts and comma. If you havé a table from a database or so, use crosstable if aou will have the Dates in one field. than you have to enter the data in brackets like this [42158] and so on

Data:
LOAD Date([Sales Agent]) As Date
INLINE [
Sales Agent
42158
42165
42172
42179
42186
42193
42200
42207
42214
];

Greetings from Munich

Martina Brenner

helpdesk@evaco.de

  +4920370900234

[#3]

Hi Martin,

My data is from Excel spreadsheet. Here is an example of what my data looks like:

Sales Agent	30.04.2015	08.05.2015	15.05.2015	22.05.2015	29.05.2015	05.06.2015
Bob	5.038,28 €	4.621,81 €	6.710,45 €	8.810,86 €	9.618,28 €	8.406,34 €
James	9.730,52 €	7.624,63 €	7.410,78 €	6.402,37 €	4.839,78 €	180,36 €
Jennifer	1.600,46 €	4.853,31 €	6.309,38 €	9.643,63 €	2.066,43 €	2.146,73 €
Ryan	782,76 €	7.623,00 €	7.981,70 €	9.852,88 €	9.429,19 €	5.619,78 €
Leslie	1.080,97 €	2.186,01 €	1.758,72 €	9.775,50 €	9.972,57 €	7.952,13 €

I use a crosstable in load. My script looks something like this:

Crosstable(Date,Sales)

LOAD

    Sales Agent,

    "42158",

    "42165",

    "42172",

    "42179",

    "42186",

    "42193"

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

Could you help? Nothing seems to work so far...

[#4]

you  could use :

date('42158','DDMMYY')  as YourDate .....

[caio_caminoski]

Hi Allen!

Please, Try:

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(num#(Date,'###0')) as Date

Resident T_Data;

DROP TABLE T_Data;

I came acrros the same issue a time ago and I only found one solution

In my case I had to reload the table after crossing and converting the data field as number. When I did the crosstable though, my dates would be sth like "42158.00000", so I had to do the following:

T_Data:

Crosstable(Date,Sales)

LOAD

    Sales Agent,

    "42158.00000",

    "42165.00000",

    "42172.00000",

    "42179.00000",

    "42186.00000",

    "42193.00000"

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

Data:

NoConcatenate

Load

    [Sales Agent],

    Left(Date,Index(Date,'.')-1) as Date

Resident T_Data;

DROP TABLE T_Data;

[daniel_kusiak]

Can you paste your SET for date:

For example:

SET DateFormat='YYYY-MM-DD';

SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';

Maybe that's a problem.

[#7]

Hi Daniel, please see below.

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

[#8]

Hmm for some reason it still doesn't work.

[daniel_kusiak]

Then try: Date(fieldname,'DD.MM.YYYY') as date

for example date([Sales Agent],'DD.MM.YYYY')as date

it works fine for me

[#10]

Hi Allen,

ok, so you have to use Crosstable, if you load your Excel table go to next twice, there you find Crosstable, enter the Name of the Datefeld and the Valuefield. Regular is only one field = Sales Agent.

Than you can load the same table again and create the datefield, NoConcetenate creates a new table with same fields, than drop the tmp table.

DataTmp:
CrossTable(Date, Value)
LOAD [Sales Agent],
[42124],
[42132],
[42139],
[42146],
[42153],
[42160]
FROM
DatumCrosstable.xlsx
(ooxml, embedded labels, table is Tabelle1);

Data:
NoConcatenate
Load [Sales Agent],
Date(Num#(Date)) As Date,
Value
Resident DataTmp;

Drop Table DataTmp;

Martina Brenner

Munich

helpdesk@evaco.de

+4920370900234

[Anonymous]

Hi

Try this

Date#(Date(Date,'MMDDYYYY')) as DateNew

Thanks