Split the data and combine

[#1]

Hello,

I've a field DateData with data like this:

DateData

2011-11-29T05:36::45.9810000

2011-11-29T05:36::45.6490000

2011-11-29T05:36::45.3140000

- So i want to generate the out for another dummy field like this

DummyDateData

2011_1129_053645_981

2011_1129_053645_649

2011_1129_053645_314

Can anyone help me on this...

[isaiah82 · Accepted solution]

Here's a sample QVW where I did this using two different methods.

1) I converted your string into a timestamp with the appropriate format code

2) I treated it as a character string and substringed it out

Which method is better just depends on your specific scenario.  Both of these assume that your input values are always the same length; e.g. contains 0's as fillers, so May would be in there as "05" instead of just "5".

Hope this helps -Isaiah

[#3]

it is working in your file but it is not working in my file. I'm using qvd file

TIMESTAMP(TIMESTAMP#(LEFT(REPLACE(LensInspectionTsA,'T',' '),24),'YYYY-MM-DD hh:mm::ss.fff'),'YYYY_MMDD_hhmmss_fff') as LensInspectionTsA1

the above code is not working.But the below code is working upto timestamp. But what format i want it is not displaying

TIMESTAMP#(LEFT(REPLACE(LensInspectionTsA,'T',' '),24),'YYYY-MM-DD hh:mm::ss.fff') as LensInspectionTsA1

[isaiah82]

So the question is, what's different between my inline data and your QVD.  Sounds like the filed in your QVD is already stored as a timestamp possibly?  You can try wrapping the fieldname in Text() and see if that works (if so another solution may be better.)  Otherwise post a sample QVD (strip out whatever you need to) and I'll have a look.  -Isaiah

[#5]

Here i'm attaching my qvd file in zip file format..Can you please work on this data.

[isaiah82]

I can't open your file ~ can you repost as a regular zip or uncompressed?  -Isaiah

[#7]

Ya i got the solution for this..

TIMESTAMP(LEFT(REPLACE(LensInspectionTsA,'T',' '),24),'YYYY_MMDD_hhmmss_fff') as DummyData