How to get the largest of numbers ?

[#1]

I have the below scenario.

ID   LXD                RSF     TCO

1     1/12/2014        345       679

2     2/12/2014        678       890

2     3/12/2014        897       890

3     2/4/2014          666       432

3     2/4/2014          760       432

3     2/4/2014          560       666

4     7/4/2014          490       987

4     7/4/2014          490        321

1. I have to get the ID  where LXD is the latest date.

2.If LXD is same get the ID based on the highest RSF

3.If LXD AND RSF is same then get the ID based on the highests TCO.

In any scenario I should get the ID only once.So ,my staright table would look like below.

ID    LXD             RSF       TCO

1     1/12/2014      345          679

2     3/12/2014      897          890

3     2/4/2014        760          432

4     7/4/2014        490          987

How can I acheive this ?Thanks much.

[dominicmander]

The most performant way to achieve this will likely be by calculating some flags in the script.

Something like

Left join (data)

Load *,

     If(ID=previous(ID), 0,1) as flag

Redisdent data

Order by ID asc, LXD desc, RSF desc, TCO desc

;

This should flag the records you need with a 1, which usually can then use in simple set analysis in your chart to limit the chart to those records only.

[Gysbert_Wassenaar]

Probably the easiest way is to create a sort key in the script:

Data:

LOAD *, Date#(LXD,'D/M/YYYY')*1000000+RSF*1000+TCO as SortKey INLINE [

    ID,LXD,RSF,TCO

    1,1/12/2014,345,679

    2,2/12/2014,678,890

    2,3/12/2014,897,890

    3,2/4/2014,666,432

    3,2/4/2014,760,432

    3,2/4/2014,560,666

    4,7/4/2014,490,987

    4,7/4/2014,490,321

];

And then use the FirstSortedValue function in the chart expressions:

FirstSortedValue(LXD, -SortKey)

FirstSortedValue(RSF, -SortKey)

FirstSortedValue(TCO, -SortKey)

[HirisH_V7]

Hi Gysbert,

Temp:
LOAD * ,
Date(LXD,'DD/MM/YYYY') as Date
INLINE [

    ID ,  LXD  ,              RSF ,    TCO
    1 ,  1/12/2014,       345,       679
    2 ,  2/12/2014,       678 ,      890
    2 ,  3/12/2014,        897  ,     890
    3 ,  2/04/2014,         666 ,      432
    3,   2/04/2014,          760,       432
    3  , 2/04/2014,         560 ,      666
    4 ,  7/04/2014,         490,       987
    4 ,  7/04/2014,         490,        321
];


Data:
Load
ID,
Date,
RSF,
TCO
Resident Temp;
Drop Table

Temp;

But here, RSF not matched according to his requierment , i will do your way creating a sort Key .

PFA,

Hirish

[HirisH_V7]

Its working Fine.

PFA,

Hirish

[#6]

Thank you,I cannot use this method as it is just a scenario but in reality I have hundreds of records.

[sunny_talwar]

Are you looking to do this in the script or front end chart object?

[#8]

Front end ...straight table.

[sunny_talwar]

Try this:

Dimension: ID

Expressions:

1) Date(Max(LXD))

2) FirstSortedValue(DISTINCT RSF, -((LXD*1000)+RSF))

3) FirstSortedValue(DISTINCT TCO, -((LXD*1000000)+(RSF*1000)+TCO))

[HirisH_V7]

Hi,

sunindia‌

Can you please explain why you have multiplied with LXD * 1000 and LXD * 1000000 like this,

-Hirish