Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
MarcoWedel

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

View solution in original post

4 Replies
Not applicable
Author

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.

ashfaq_haseeb
Champion III
Champion III

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

ashfaq_haseeb
Champion III
Champion III

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

MarcoWedel

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