Distinct Straight Table Values

[danielnevitt]

Hi, I have the following data and I am struggling to create a table showing only distinct HDR_NUM's: Office    Company          Pricing_End_Date    HDR_NUM    MonthYear UK        ABC                31/08/2013              8085878        Aug-2013 UK        ABC                30/09/2013              8085878        Sep-2013 UK        ABC                31/10/2013              8085878        Oct-2013 UK        DEF                31/08/2013              8086989        Aug-2013 UK        DEF                30/09/2013              8086989        Sep-2013 UK        DEF                31/10/2013              8086989        Oct-2013 Ideally I would like to show the data by distinct HDR_NUM and showing the latest Pricing_End_Date: HDR_NUM      Office    Company    Pricing_End_Date      MonthYear 8085878          UK        ABC            31/10/2013                Oct-2013 8086989          UK        DEF            31/10/2013                Oct-2013 Thanks for any help with this. Regards, Daniel

[#2 · Accepted solution]

Whaterver you create (listbox, table, graph with HDR_NUM as a dimension and as an expression...), will show all the distinct values of that field.

[vikasmahajan]

count distinct(HDR_NUM)

[MayilVahanan]

Hi

Try like this

Test:

Load * Inline

[

Office,Company,Pricing_End_Date,HDR_NUM,MonthYear

UK,ABC,31/08/2013,8085878,Aug-2013

UK,ABC,30/09/2013,8085878,Sep-2013

UK,ABC,31/10/2013,8085878,Oct-2013

UK,DEF,31/08/2013,8086989,Aug-2013

UK,DEF,30/09/2013,8086989,Sep-2013

UK,DEF,31/10/2013,8086989,Oct-2013

];

Load Company&PricingEndDate as Key;

Load

Company,

Date(max(Pricing_End_Date)) as PricingEndDate

Resident Test

Group by Company;

Inner Join

Load

Office,

Company&Pricing_End_Date As Key,

Company,

Pricing_End_Date,

HDR_NUM,

MonthYear

Resident Test;

Drop Table Test;

[danielnevitt]

Hi,

Thanks for your help.  That all worked great.

Can you help with showing each company and then the MonthName field ie:

Company  Aug-13  Sep-13  Oct-13  Nov-13

ABC         10         5          8          15

The month should be a count of the MonthName.

Hope that makes sense.

Regards,

Daniel