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

Stuck with date formatting to get Dates in common format

Mon, 17 Nov 2014 15:57:56 GMT

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

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

Mon, 17 Nov 2014 16:24:21 GMT

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

ashfaq_haseeb — Mon, 17 Nov 2014 16:32:40 GMT

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

ashfaq_haseeb — Mon, 17 Nov 2014 16:37:40 GMT

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

MarcoWedel — Mon, 17 Nov 2014 16:46:55 GMT

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