Extract only distinct values from date

[viveksingh]

Hi Folks,

I Have a date filed which is having date like

DATE

11/12/2013 11:00:00

11/12/2013 12:00:00

11/12/2013 12:30:00.

10/12/2013 09:00:00

10/12/2013 10:00:00

when i used

date(DATE,'MM-YYYY') as MONTH_YEAR. When I use this MONTH_YEAR to list box it is showing duplicate values also.

Please help me in getting only unique records.

below is what it is showing me in list box

LISTBOX

11-2013

11-2013

11-2013

10-2013

10-2013

Expected:

11-2013

10-2013

[sunny_talwar]

Try adding MonthStart() function:

Date(MonthStart(DATE),'MM-YYYY') as MONTH_YEAR

[sinanozdemir]

Hi,

stalwar1answer works pretty well. I also use Floor() function with time stamps. Just another way of doing it.

LOAD Date(Floor(Timestamp#(DATE, 'MM/DD/YYYY hh:mm:ss')), 'MM-YYYY') As DATE INLINE [
  DATE
  11/12/2013 11:00:00
  11/12/2013 12:00:00
  11/12/2013 12:30:00.
  10/12/2013 09:00:00
  10/12/2013 10:00:00
];

Thanks

[sunny_talwar]

But Floor alone won't work if you have other dates

LOAD Date(Floor(Timestamp#(DATE, 'MM/DD/YYYY hh:mm:ss')), 'MM-YYYY') As DATE INLINE [  
  DATE  
  11/12/2013 11:00:00  
  11/12/2013 12:00:00  
  11/13/2013 12:00:00

  11/12/2013 12:30:00
  10/12/2013 09:00:00  
  10/12/2013 10:00:00  
]; 

[sinanozdemir]

It actually worked. Since you are only using MM-YYYY format, it kinda groups them:

[sunny_talwar]

I am seeing this

With this added

LOAD Date(Floor(Timestamp#(DATE, 'MM/DD/YYYY hh:mm:ss')), 'MM-YYYY') As DATE INLINE [   

  DATE   

  11/12/2013 11:00:00   

  11/12/2013 12:00:00   

  11/13/2013 12:00:00

  11/12/2013 12:30:00 

  10/12/2013 09:00:00   

  10/12/2013 10:00:00   

];

[sinanozdemir]

I didn't see "11/13/2013". It would only work based on the provided data.

[sunny_talwar]

‌Make sense   . Apologize for not being clear about it earlier.

Best

Sunny

[swuehl]

vivek,

distinctness of dual field values (like dates in QV) is determined by the numeric representation.

Date() function will only change the text representation, not the number, that's why you see duplicates.

You need to change the number, either by using a rounding function or date & time function that transforms the timestamp, like Sunny suggested (and what would be the standard approch) or by using interpretation function Date#() to change the number in addition to the format function Date():

Date#(Date(DATE,'MM-YYYY'),'MM-YYYY') as MONTH_YEAR

Why don’t my dates work?

Data Types in QlikView

Get the Dates Right

[sunny_talwar]

Are you able to resolve the issue Vivek? If you have, I would suggest closing this thread by marking the correct response. In case if you still have issues, please let us know and we might be able to help.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny