topic Extract only distinct values from date in QlikView

Extract only distinct values from date

viveksingh — Wed, 25 Nov 2020 16:16:04 GMT

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

10-2013

Expected:

11-2013

10-2013

Re: Extract only distinct values from date

sunny_talwar — Fri, 16 Sep 2016 19:02:53 GMT

Try adding MonthStart() function:

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

Re: Extract only distinct values from date

sinanozdemir — Fri, 16 Sep 2016 20:28:01 GMT

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

Re: Extract only distinct values from date

sunny_talwar — Fri, 16 Sep 2016 20:29:54 GMT

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

Re: Extract only distinct values from date

sinanozdemir — Fri, 16 Sep 2016 20:32:41 GMT

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

Re: Extract only distinct values from date

sunny_talwar — Fri, 16 Sep 2016 20:35:53 GMT

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

];

Re: Extract only distinct values from date

sinanozdemir — Fri, 16 Sep 2016 20:45:10 GMT

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

Re: Extract only distinct values from date

sunny_talwar — Fri, 16 Sep 2016 20:49:19 GMT

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

Best

Sunny

Re: Extract only distinct values from date

swuehl — Fri, 16 Sep 2016 20:50:53 GMT

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

Re: Extract only distinct values from date

sunny_talwar — Sat, 17 Sep 2016 23:48:43 GMT

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