Date Filtering Issue While Loading

[#1]

Hi All,

Please find the attached QVW and QVD.

When i filter the date field in the Load Script, I am getting some 225 records but there are actually 625 records.

Can someone please check the Qvw.

Thanks in advance.

Regards,

Keerthi KS

[Gysbert_Wassenaar]

It's all the double spaces. Try replacing the double spaces with single spaces:

TimeStamp(TimeStamp#(Replace(LastModifiedOn,'  ',' '), 'MMM D YYYY h:mmTT'))

[#3]

When i load complete data and check it was around 625 where the dates are greater than 13 Feb 2016.

[Chanty4u]

why this condition

it gives u the  the date  > than records only i think

Where Timestamp#(LastModifiedOn, 'MMM D YYYY hh:mmTT')

>Timestamp#('13/2/2016', 'DD/M/YYYY');

[#5]

Yes i need only records that are greater than 13/2/2016.

But its not giving correct data.

[settu_periasamy · Accepted solution]

Hi,

It seems there are 4 different timestamp formats (with single space,double space) in the qvd. first convert it, then check.

Alt(Date#(LastModifiedOn,'MMM  DD YYYY  hh:mmTT'),

      Date#(LastModifiedOn,'MMM DD YYYY hh:mmTT'),

      Date#(LastModifiedOn,'MMM  DD YYYY hh:mmTT'),

      Date#(LastModifiedOn,'MMM DD YYYY  hh:mmTT'))) as New

Edit

Sample Attached

[#7]

Please find the attached qvw. I Have selected the data and it shows only 626 records

[tamilarasu]

You can try,

LOAD LastModifiedBy,

     LastModifiedOn,

     Uniqueid

FROM

(qvd) Where  Date(Floor(TimeStamp#(Replace(LastModifiedOn,'  ',' '), 'MMM D YYYY hh:mmTT')), 'DD/MM/YYYY') > '13/02/2016' ;

[Chanty4u]

but chk this..

[Kushal_Chawda]

try

WHERE Date(Floor(TimeStamp#(purgechar(LastModifiedOn,' '), 'MMMDDYYYYhh:mmTT')), 'DD/MM/YYYY') > date#('13/02/2016' ,'DD/MM/YYYY');