topic Re: Date Filtering Issue While Loading in QlikView

Date Filtering Issue While Loading

Tue, 29 Mar 2016 06:36:32 GMT

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

Re: Date Filtering Issue While Loading

Gysbert_Wassenaar — Tue, 29 Mar 2016 06:43:02 GMT

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

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

Re: Date Filtering Issue While Loading

Tue, 29 Mar 2016 06:45:59 GMT

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

Re: Date Filtering Issue While Loading

Chanty4u — Tue, 29 Mar 2016 06:46:58 GMT

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');

Re: Date Filtering Issue While Loading

Tue, 29 Mar 2016 06:51:20 GMT

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

But its not giving correct data.

Re: Date Filtering Issue While Loading

settu_periasamy — Tue, 29 Mar 2016 06:56:16 GMT

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'))) as New

Edit

Sample Attached

Re: Date Filtering Issue While Loading

Tue, 29 Mar 2016 06:57:31 GMT

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

Re: Date Filtering Issue While Loading

tamilarasu — Tue, 29 Mar 2016 07:02:18 GMT

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' ;

Re: Date Filtering Issue While Loading

Chanty4u — Tue, 29 Mar 2016 07:04:29 GMT

but chk this..

Re: Date Filtering Issue While Loading

Kushal_Chawda — Tue, 29 Mar 2016 07:12:09 GMT

try

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