topic Re: String date field in QlikView

String date field

Thu, 19 Dec 2013 09:31:51 GMT

Hello Guys!

I have a string date field and when I try to divide into year,month,day,hh,mi, It does not work for me..My date field like "201312191130" like "YYYYMMDDHHMIMI" and I want to show it seperate in graph like month,day,hour or minute..

Hope you have a solution...

kind regards..

Re: String date field

Anonymous — Thu, 19 Dec 2013 09:37:03 GMT

for date use>>

MakeDate(left(201312191130,4),mid(201312191130,5,2),mid(201312191130,7,2))

for time use >>

MakeTime(mid(201312191130,9,2),mid(201312191130,11,2))

Regards

Nitin

Re: String date field

Thu, 19 Dec 2013 09:38:55 GMT

Hi ATACAN

if it is a real date field so you can use year(date), month(date), day(date) and so on

if it is a string or numeric field use left and mid functions and then rearrange a date format with makedate

ex :

left(date, 4) --> year

mid(date(5,2) --> month

makedate(left(date, 4), mid(date(5,2)) --> 01/12/2013

best regards

Chris

Re: String date field

hic — Thu, 19 Dec 2013 10:14:32 GMT

I think it is bad practice to use string functions to interpret dates, when there are good interpretation functions. In your case, you should use

TimeStamp#('201312191130','YYYYMMDDhhmm')

Using this, it is straightforward to extract year, month, etc:

Year(TimeStamp#('201312191130','YYYYMMDDhhmm'))

Month(TimeStamp#('201312191130','YYYYMMDDhhmm'))

Date(MonthStart(TimeStamp#('201312191130','YYYYMMDDhhmm')),'YYYY-MMM') as RollingMonth

HIC

Re: String date field

Thu, 19 Dec 2013 10:50:51 GMT

Thanks chris...It does work on it! However, I have another trouble...When I change the month names from English to Turkish I still have seen on my report English month names...My settings are at below;

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm TT';

SET DateFormat='DDMMMM';

SET TimestampFormat='YYYYMMDDhhmm';

SET MonthNames='Ocak;Subat;Mart;Nisan;May;Haziran;Temmuz;Augustos;Eylul;Ekim;Kasim;Aralik';

SET DayNames='Pazartesi;Sali;Carsamba;Persembe;Cuma;Cumartesi;Pazar';

Can you help me about language problem?

Thanks...

Re: String date field

Thu, 19 Dec 2013 10:54:59 GMT

Hi Henric,

I know it is not good way to be practiced with date formatting stuff..However, my client sent me date format as a string and have no way right now to use the format on qlikvliew...I asked to Chris and I want to ask it to you as well...I have changed date field and got the wanted formatting type of date..However, I see that English month names and I want to see it on my local langauge names..How can I get the turkish names in my report while reloading the data.?

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm TT';

SET DateFormat='DDMMMM';

SET TimestampFormat='YYYYMMDDhhmm';

SET MonthNames='Ocak;Subat;Mart;Nisan;May;Haziran;Temmuz;Augustos;Eylul;Ekim;Kasim;Aralik';

SET DayNames='Pazartesi;Sali;Carsamba;Persembe;Cuma;Cumartesi;Pazar';

Best Regards,

Re: String date field

hic — Thu, 19 Dec 2013 11:46:19 GMT

Because you have defined your environment variables incorrectly. You have

SET DateFormat='DDMMMM';

when you should have

SET DateFormat='DD MMM YY';

Further, I suggest you use

SET MonthNames='Oca;Sub;Mar;Nis;May;Haz;Tem;Aug;Eyl;Eki;Kas;Ara';

SET LongMonthNames='Ocak;Subat;Mart;Nisan;May;Haziran;Temmuz;Augustos;Eylul;Ekim;Kasim;Aralik';

Then, MMM will mean the short names and MMMM will mean the long names.

Finally, about string functions: You can use Timestamp#() to convert the string to a date. I does exactly what you want. See more on http://community.qlik.com/docs/DOC-3102.

HIC

Re: String date field

Thu, 19 Dec 2013 11:54:15 GMT

Thanks a lot Henric! I know the question is quite simple and easy for you but if you dont know and at starting level eveything is getting harder...lol....

I really appreciated in you to reply me quickly...