Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Stuck with date formatting to get Dates in common format

Hi friends my source data is a .txt file without any separator and column names we are differentiating each column with common template.My problem is [@57:63] as [Accounting Period] contains different Date Formats as below while writing date#([@57:63] )

0201104

0201403

0201407

2011030

Need to have YYYYMM and to remove 0 Before and after YYYYMM

     [@26:27] as [AccLn],

     [@28:30] as [Prod],

     [@31:40] as [Period],

     [@41:46] as [Cert],

     [@47:56] as  [Claim],

     [@57:63] as [Accounting Period]=>Date(Date#([@57:63],'DDMMYYYY'),'YYYYMM') getting Error

Can any one please suggest to get in Accurate Format as YYYYMM

Tags (3)
1 Solution

Accepted Solutions

Re: Stuck with date formatting to get Dates in common format

=Date#(Left(Num([@57:63]),6),'YYYYMM') as [Accounting Period]

4 Replies
Not applicable

Re: Stuck with date formatting to get Dates in common format

Load

     [@1:3]  as [Rep],

     [@4:6] as [Curr],

     [@7:8] as [Org],

     [@9:10] as [Mgr],

     [@11:12] as [Maj],

     [@13:14] as [Min],

     [@15:21] as  [Iss],

     [@22:23] as   [Prd],

     [@24:25] as  [Tran],

     [@26:27] as [AccLn],

     [@28:30] as [Prod],

     [@31:40] as [Pol],

     [@41:46] as [Cert],

     [@47:56] as  [Claim],

     [@57:63] as [AccPrd],==>Date Formats differently need to get in common format of 'YYYYMM'

[@1:3]&[@57:63] as Key,

My problem is while writing Date functions on [@57:63] not getting in desired format getting errors .can any one please do needful.Thanks.

Re: Stuck with date formatting to get Dates in common format

Hi Srikant,

Try this and let me know if that works.

Load *,Date(date#(replace(trim(replace(Date, '0', ' ')), ' ', 0),'YYYYMM'),'YYYY-MM') as NewDate

      ,Date(date#(replace(trim(replace(Date, '0', ' ')), ' ', 0),'YYYYMM'),'YYYYMM') as NewDate1;

load * Inline

[

Date

0201104

0201403

0201407

2011030

];

You can format the date in a way you like.

Regards

ASHFAQ

Re: Re: Stuck with date formatting to get Dates in common format

As per your script try this

LOAD

     [@1:3]  as [Rep],

     [@4:6] as [Curr],

     [@7:8] as [Org],

     [@9:10] as [Mgr],

     [@11:12] as [Maj],

     [@13:14] as [Min],

     [@15:21] as  [Iss],

     [@22:23] as   [Prd],

     [@24:25] as  [Tran],

     [@26:27] as [AccLn],

     [@28:30] as [Prod],

     [@31:40] as [Pol],

     [@41:46] as [Cert],

     [@47:56] as  [Claim],

     [@57:63] as [AccPrd],

     Date(date#(replace(trim(replace([@57:63], '0', ' ')), ' ', 0),'YYYYMM'),'YYYYMM') as Date,

[@1:3]&[@57:63] as Key

FROM

[My Sample.txt]

(fix, codepage is 1256);

Regards

ASHFAQ

Re: Stuck with date formatting to get Dates in common format

=Date#(Left(Num([@57:63]),6),'YYYYMM') as [Accounting Period]

Community Browser