Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
martin_hamilton
Contributor

Duplicate values in filter pane - qlik sense

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

1 Solution

Accepted Solutions
Highlighted
caio_caminoski
Contributor

Re: Duplicate values in filter pane - qlik sense

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

3 Replies
ahaahaaha
Honored Contributor

Re: Duplicate values in filter pane - qlik sense

Hi Martin,

Try to convert the calculated date into text.

Regards,

Andrey

Highlighted
caio_caminoski
Contributor

Re: Duplicate values in filter pane - qlik sense

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
Contributor

Re: Duplicate values in filter pane - qlik sense

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

Community Browser