topic Re: maximum date in QlikView

maximum date

Tue, 15 Sep 2015 15:23:42 GMT

Hi All,

My data looks like this as below.

Customer ¦ Product number ¦ Date

A 222222 10/11/2011

B 222222 11/11/2014

C 233334 10/07/2015

D 233334 10/08/2014

My output should be like this below.

Customer ¦ Product number ¦ Date

B 222222 11/11/2014

C 233334 10/07/2015

So basically if the same product number then it should display only the latest date with the customer detail. No old record should be shown.

I tried couple of ways using expression but it shows all the records and only highlights the latest one/.

Please advise here.

Re: maximum date

sunny_talwar — Tue, 15 Sep 2015 15:27:14 GMT

Try this:

Dimension: [Product number]

Expression:

1) FirstSortedValue(Customer, -Date)

2) FirstSortedValue(Date, -Date)

Re: maximum date

sunny_talwar — Tue, 15 Sep 2015 15:29:55 GMT

Have a look at the attached:

Re: maximum date

Anonymous — Tue, 15 Sep 2015 15:32:57 GMT

Hi Rathish,

Maybe:

Table:

Load [Product number],

max(Date) as Date

Resident ...

Group by [Product number];

left join(Table)

Load

Customer,

[Product number],

Date

Resident...

Re: maximum date

MK_QSL — Tue, 15 Sep 2015 15:33:26 GMT

if you have Date and Product number repeating for same Customer, use below formulas....

FirstSortedValue(DISTINCT Customer, -Date)

FirstSortedValue(DISTINCT Date, -Date)

Re: maximum date

Tue, 15 Sep 2015 15:48:37 GMT

I tried this but I am not getting filtered. its displays the same with all values

For this FirstSortedValue(Date, -Date) I get the difference in integers like 422336.. 43336 etcc.

Re: maximum date

Tue, 15 Sep 2015 15:55:23 GMT

Hi Both Manish and sunny is working. But iam getting this part

FirstSortedValue(Date, -Date)

FirstSortedValue(DISTINCT Date, -Date) for both as integers ..dates is not getting reflected..

Neetu 's need to check..

Re: maximum date

Tue, 15 Sep 2015 16:01:06 GMT

Hi Rathish,

use this load script or see the attachment:

Table:

LOAD * Inline [

Customer, Product number, Date

A, 222222, 10/11/2011

B, 222222, 11/11/2014

C, 233334, 10/07/2015

D, 233334, 10/08/2014

];

TableOut:

Load [Product number],

Max(Date) as Date

Resident Table

Group by [Product number];

left join

Load Customer, [Product number], Date

Resident Table;

Drop Table Table;

Re: maximum date

sunny_talwar — Tue, 15 Sep 2015 16:07:58 GMT

Try this:

Date(FirstSortedValue(Date, -Date)) or

Date(FirstSortedValue(DISTINCT Date, -Date))

Re: maximum date

Tue, 15 Sep 2015 21:16:35 GMT

Hi Guys,

It works good. Their is another way also ..from the presentation tab we could use the date option.

Re: maximum date

sunny_talwar — Tue, 15 Sep 2015 21:19:09 GMT

Yup you are right.

I am glad you were able to figure it out. If you got what you were looking for, I would suggest closing the thread by marking the correct answer and and helpful answers.

Best,

Sunny

Re: maximum date

Wed, 16 Sep 2015 18:23:48 GMT

Hi All,

Their is one more issue iam facing through for the same..

here i have one more date column

Customer ¦ Product number ¦ Date ¦ LastDate

A 222222 10/11/2011 10/11/2012

B 222222 11/11/2014 10/11/2013

C 233334 10/07/2015 10/07/2015

D 233334 10/08/2014 10/07/2015

so when i check the same product number i need to check both the date columns and consider the max date//

Re: maximum date

sunny_talwar — Thu, 17 Sep 2015 04:25:46 GMT

What is the expected output with regards to your new sample data set here?

Re: maximum date

Thu, 17 Sep 2015 06:12:13 GMT

Hi Sunny,

Here the output needs to be.. it has to compare all the 4 dates for ex product number 22222 - 11/11/2014 is the max date so we need to consider the output as below and if you see the data for C and D the date columns 10/07/2015 are same across C and D . So

B 222222 11/11/2014 10/11/2013

ID ¦Customer ¦ Product number ¦ Date ¦ LastDate

1 A 222222 10/11/2011 10/11/2012

2 B 222222 11/11/2014 10/11/2013

3 C 233334 10/07/2015 10/07/2015

4 D 233334 10/08/2014 10/07/2015

and if you see the data for C and D the date columns 10/07/2015 are same across C and D . So in such a situation it has to have a second order via ID and take the the highest ID

Re: maximum date

sunny_talwar — Thu, 17 Sep 2015 06:20:22 GMT

So between C and D, which row would you like to see in the output?

Re: maximum date

Thu, 17 Sep 2015 06:30:26 GMT

in C and D .as the dates are similar.. I ahould check thru id

it should be

4 D 233334 10/08/2014 10/07/2015

Re: maximum date

sunny_talwar — Thu, 17 Sep 2015 07:04:05 GMT

See if this helps:

Dimension: Product number

Expressions:

=If(FirstSortedValue(DISTINCT RangeMax(Date, LastDate), -RangeMax(Date, LastDate)) =

FirstSortedValue(DISTINCT RangeMax(Date, LastDate), RangeMax(Date, LastDate)),

FirstSortedValue(Customer, -ID),

FirstSortedValue(Customer, -RangeMax(Date, LastDate)))

=If(FirstSortedValue(DISTINCT RangeMax(Date, LastDate), -RangeMax(Date, LastDate)) =

FirstSortedValue(DISTINCT RangeMax(Date, LastDate), RangeMax(Date, LastDate)),

FirstSortedValue(ID, -ID),

FirstSortedValue(ID, -RangeMax(Date, LastDate)))

=If(FirstSortedValue(DISTINCT RangeMax(Date, LastDate), -RangeMax(Date, LastDate)) =

FirstSortedValue(DISTINCT RangeMax(Date, LastDate), RangeMax(Date, LastDate)),

FirstSortedValue(Date, -ID),

FirstSortedValue(Date, -RangeMax(Date, LastDate)))

=If(FirstSortedValue(DISTINCT RangeMax(Date, LastDate), -RangeMax(Date, LastDate)) =

FirstSortedValue(DISTINCT RangeMax(Date, LastDate), RangeMax(Date, LastDate)),

FirstSortedValue(LastDate, -ID),

FirstSortedValue(LastDate, -RangeMax(Date, LastDate)))

Re: maximum date

Thu, 17 Sep 2015 08:15:08 GMT

Hi Sunny,

This is yielding good results. but when I use a search button and for example search product number 222222

I am getting good result i.e only 1 field

B 22222 B ....

But when I search using customer number for example A

my report shows still

1 A 222222 10/11/2011 10/11/2012

is it normal?

Re: maximum date

Thu, 17 Sep 2015 08:18:49 GMT

Hi sunny,

is their a way to restrict when selecting the customer number as well?

Re: maximum date

Thu, 17 Sep 2015 12:44:04 GMT

hi sunny..

was the explanation clear?