topic Re: Need Help Converting a Date in CSV file in QlikView

Need Help Converting a Date in CSV file

Tue, 15 Jul 2014 14:43:20 GMT

Good Morning All;

I am having a problem converting a Date from our vendor. The file is coming to us as several CSV files. Here are a few of the dates found in the IFADate field:

00:12.9

11:19.3

51:29.8

If I use Excel 2007 and format the cells using date (mm/dd/yy hh:ss) all translate as 1/0/1900 *****. But if I use Excel 2010 the dates will be formatted properly. Rather than "editing" the CSV files before load I would like QlikView to do it in the Load process.

Any Ideas?

Thanks

Mike

Re: Need Help Converting a Date in CSV file

maxgro — Tue, 15 Jul 2014 14:50:32 GMT

=date(date#('51:29.8', 'YYYY:DD.MM'))

=date(date#(IFADate , 'YYYY:DD.MM'))

Re: Need Help Converting a Date in CSV file

hic — Tue, 15 Jul 2014 14:59:50 GMT

Depends on what the numbers represent... are they minutes, seconds and decimal seconds, or? If so I would use

Time#(IFADate, 'mm:ss.fff')

But QlikView still doesn't know of which month, day or hour these minutes belong. So you need to supply that, e.g. by

Time( MakeTime(12) + Time#(IFADate, 'mm:ss.fff'), 'hh:mm:ss.fff' ) as Time

TimeStamp( MakeDate(2014,07,15) + MakeTime(12) + Time#(IFADate, 'mm:ss.fff'), 'YYYY-MM-DD hh:mm:ss.fff' ) as TimeStamp

HIC

Re: Need Help Converting a Date in CSV file

Tue, 15 Jul 2014 15:07:07 GMT

Thanks...output looks different. Where I was getting nothing now the dates are as follows:

00:12.9 translates as 36,869

09:12.4 as 40,151

27:12.4 as 46,725

So I am getting thing to change but not all dates are converting. Only the 3 dates above. There are 19 others

Thanks again.

Re: Need Help Converting a Date in CSV file

maxgro — Tue, 15 Jul 2014 15:11:49 GMT

could you post the csv file with the dates (just the dates field)?

Re: Re: Need Help Converting a Date in CSV file

Tue, 15 Jul 2014 15:22:07 GMT

Here you go...InvFollowup.csv

Re: Need Help Converting a Date in CSV file

PrashantSangle — Tue, 15 Jul 2014 15:22:22 GMT

Hi,

Try this,

date(

alt(

date#(IFADate , 'YYYY:DD.MM'),

date#(IFADate , 'MM:DD:YYYY'),

date#(IFADate , 'DD:MM:YYYY'),

)

, 'YYYY:MM:DD' )

Regards,

Re: Re: Need Help Converting a Date in CSV file

Tue, 15 Jul 2014 15:33:14 GMT

Here you go...InvFollowup.csv

Not sure what happened to the first

Re: Need Help Converting a Date in CSV file

PrashantSangle — Tue, 15 Jul 2014 15:52:12 GMT

Hi,

I am not sure but your IFADate contain only time

there is no date values contain in IFADate

Regards,