Straight table

[pcostapa]

Hi,

Giving the sample data:

LOAD * INLINE [

    year, name, office

    2009, pau, D

    2010, pau, D

    2011, pau, C

    2012, pau, C

    2013, pau, B

    2014, pau, A

    2010, pep, D

    2011, pep, D

    2012, pep, C

    2013, pep, C

    2014, pep, B

    2015, pep, A

    2014, joan, B

    2015, joan , B

    2016, joan , D

];

I need to show for each user which office is the last one.

The expected result in a straight table show be:

name	office
pau	A
pep	A
joan	D

Any suggestions?

Thanks

[sunny_talwar · Accepted solution]

Try this as your expression in a straight table object

=FirstSortedValue(office, -year)

[Kushal_Chawda]

Create straight table

Dimension:

name

Expression:

only({<Year={"$(=max(Year))"}>}Office)

in script:

Data:

LOAD * INLINE [

    year, name, office

    2009, pau, D

    2010, pau, D

    2011, pau, C

    2012, pau, C

    2013, pau, B

    2014, pau, A

    2010, pep, D

    2011, pep, D

    2012, pep, C

    2013, pep, C

    2014, pep, B

    2015, pep, A

    2014, joan, B

    2015, joan , B

    2016, joan , D

];

inner join(Date)

LOAD name,

          max(year) as year

resident data

group by name;

[sunny_talwar]

Or you can create flag in the script:

Table:

LOAD * INLINE [

    year, name, office

    2009, pau, D

    2010, pau, D

    2011, pau, C

    2012, pau, C

    2013, pau, B

    2014, pau, A

    2010, pep, D

    2011, pep, D

    2012, pep, C

    2013, pep, C

    2014, pep, B

    2015, pep, A

    2014, joan, B

    2015, joan , B

    2016, joan , D

];

Left Join (Table)

LOAD Max(year) as year,

  name,

  1 as Flag

Resident Table

Group By name;

and then try this expression:

=Only({<Flag = {1}>}office)

[pcostapa]

Thanks is works!