Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

marksmunich
Contributor III

resolving and fixing the field value

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.

Year_Month.JPG.jpg

Thanks

Mark

6 Replies

Re: resolving and fixing the field value

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

sunilkumarqv
Valued Contributor II

Re: resolving and fixing the field value

Try like this

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

or

use in script

Set DataFormat='DD/MM/YYY'

sivarajs
Valued Contributor II

Re: resolving and fixing the field value

purgechar(field,'_')

Use in script or list box expression

MVP
MVP

Re: resolving and fixing the field value

if you have mixed format you can use the alt function

M:

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

LOAD * INLINE [

    YM

    2014_01

    2014_02

    201403

    201404

];

er_mohit
Honored Contributor II

Re: resolving and fixing the field value

Try this in script

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

or

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

preminqlik
Valued Contributor II

Re: resolving and fixing the field value

hi try this

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