Duplicate values in filter pane - qlik sense

[martin_hamilton]

within a filter pane duplicate values are being displayed.

I am converting a date format of the following:

18-APR-17 14.13.11.039000000

using the following load script:

Date(Timestamp#(DATE_CREATED,'DD-MMM-YY hh.mm.ss.fffffffff'),'DD/MM/YYYY') as DateField

It looks like some hidden values are being included hence its not distinct within the column?

any ideas?

i have tried differing options such as Floor but doesnt seem to work. I also thought about splitting the field but that becomes messy by the look of it.

Thank You

Martin

[ahaahaaha]

Hi Martin,

Try to convert the calculated date into text.

Regards,

Andrey

[caio_caminoski · Accepted solution]

Hi Martin,

Try the following:

Date(Floor(Timestamp#(DATE_CREATED,'DD-MMM-YY hh.mm.ss.fffffffff')),'DD/MM/YYYY') as DateField

I hope it works.

You said you used floor and it didn't work. Another possible option would be:

Table_Name:

Load *,

     Makedate(Year(DateField1).Month(DateField1),day(DateField1)) AS DateField;

Load

     ...,

     Date(Timestamp#(DATE_CREATED,'DD-MMM-YY hh.mm.ss.fffffffff'),'DD/MM/YYYY') as DateField1

Resident .... ;

Drop Field DateField1;

Regards,

Caio Caminoski

[martin_hamilton]

Thanks in the end I used the following:

Date(Floor(DATE_CREATED), 'DD/MM/YYYY') as [Session Date]

I must have made a mistake previously.

Thanks

Martin

[PavanAlapati]

Thanks for the solution to display Distinct values in Filter pane.

Date(Floor(DATE_CREATED), 'DD/MM/YYYY') as [Session Date] -- Working perfectly.