topic Re: Distinct Straight Table Values in QlikView

Distinct Straight Table Values

danielnevitt — Fri, 30 Aug 2013 10:50:40 GMT

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

Re: Distinct Straight Table Values

Fri, 30 Aug 2013 10:58:29 GMT

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.

Re: Distinct Straight Table Values

vikasmahajan — Fri, 30 Aug 2013 11:01:36 GMT

count distinct(HDR_NUM)

Re: Distinct Straight Table Values

MayilVahanan — Fri, 30 Aug 2013 11:11:58 GMT

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;

Re: Distinct Straight Table Values

danielnevitt — Fri, 30 Aug 2013 13:28:31 GMT

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