topic Re: resolving and fixing the field value in QlikView

resolving and fixing the field value

marksmunich — Mon, 24 Feb 2014 08:39:47 GMT

Hello Community,

I have a problem in my application with a field. The values in this field are uneven. how can i fix it. i want to make all the values in the format year_Month. below is the image.

Thanks

Mark

Re: resolving and fixing the field value

Peter_Cammaert — Mon, 24 Feb 2014 08:47:47 GMT

Do you have 2 possible formats? Then, when loading the different date fields in your script, test for the presence of an underscore. If it isn't there, format the purely numerical value with date(date#(NumericalDateValue, 'YYYYMM01'), 'YYYY_MM') or something.

Peter

Re: resolving and fixing the field value

sunilkumarqv — Mon, 24 Feb 2014 08:52:05 GMT

Try like this

Date(Date,'DD/MM/YYYY')

use in script

Set DataFormat='DD/MM/YYY'

Re: resolving and fixing the field value

sivarajs — Mon, 24 Feb 2014 09:06:10 GMT

purgechar(field,'_')

Use in script or list box expression

Re: resolving and fixing the field value

maxgro — Mon, 24 Feb 2014 09:18:03 GMT

if you have mixed format you can use the alt function

LOAD YM, date(alt(date#(YM, 'YYYY_MM'), Date#(YM, 'YYYYMM')), 'YYYY_MM') as NewYM;

LOAD * INLINE [

2014_01

2014_02

201403

201404

];

Re: resolving and fixing the field value

er_mohit — Mon, 24 Feb 2014 09:20:21 GMT

Try this in script

date(Date#(PurgeChar(Datefield,'_'),'YYYYMM'),'YYYYMM') AS Date

date(PurgeChar(Datefield,'_'),'YYYYMM') AS Date

Re: resolving and fixing the field value

preminqlik — Mon, 24 Feb 2014 09:34:18 GMT

hi try this

date(date#(replace(TransDate,'_',null()),'YYYYMM'),'YYYYMM') as NEWDate