calculated dimension

[#1]

Hi Experts,

I have a table which has fields Product no, Delivery details.

below are some sample field values.  

Product no	Delivery Details
S125	Delivered at Croydon
	Not Delivered
S126	Not Delivered
	Delivered at Birmingham
S130	Not Delivered
S140	Delivered at Reading
	Not Delivered

I need to populate this in a pivot table, but i want to display one line per Product.

For ex. if a product has two lines Delivered at... & Not Delivered, then i want to display 'Delivered at..'

if a product has one line, display as it is.

The result should be,

Product no	Delivery Details
S125	Delivered at Croydon
S126	Delivered at Birmingham
S130	Not Delivered
S140	Delivered at Reading

i dont want to do this in script. i want this in table.

Please somebody help me

[sunny_talwar]

Is there a date field with each of the rows of data? May be delivered thing would have a newer dates?

Dimension 1

Product no

Aggr(FirstSortedValue([Delivery Details], -Date), [Product no])

[#3]

Sunny,

Yes. there is a date field. but there are same dates for each product No.

Here is an example

Product no	Date	Delivery Details
S125	01/09/2016	Delivered at Croydon
	01/09/2016	Not Delivered
S126	15/09/2016	Not Delivered
	15/09/2016	Delivered at Birmingham
S130	20/09/2016	Not Delivered
S140	03/10/2016	Delivered at Reading
	03/10/2016	Not Delivered

[jyothish8807]

Hi Rajendran,

Try to create a calculated dimension:

If(len(Product no)>0,Product no)

Regards

KC

[#5]

No its not working

[sunny_talwar]

Check out the attached

[jyothish8807]

Hi Rajendran,

What all dimensions you have ? And what is your expression ?

Regards

KC

[sunny_talwar]

Or this:

Table:

LOAD * INLINE [

    Product no, Date, Delivery Details

    S125, 01/09/2016, Delivered at Croydon

    S125, 01/09/2016, Not Delivered

    S126, 15/09/2016, Not Delivered

    S126, 15/09/2016, Delivered at Birmingham

    S130, 20/09/2016, Not Delivered

    S140, 03/10/2016, Delivered at Reading

    S140, 03/10/2016, Not Delivered

];

Left Join (Table)

LOAD [Delivery Details],

  1 as Flag

Resident Table

Where WildMatch([Delivery Details], 'Delivered at *');

Left Join (Table)

LOAD [Product no],

  If(Count(DISTINCT [Delivery Details]) > 1, 1, 0) as Flag2

Resident Table

Group By [Product no];

[#9]

Sunny,

i tried your first method, it worked fine for the below values

Product no	Date	Delivery Details
S125	01/09/2016	Delivered at Croydon
	01/09/2016	Not Delivered
S126	15/09/2016	Not Delivered
	15/09/2016	Delivered at Birmingham
S130	20/09/2016	Not Delivered
S140	03/10/2016	Delivered at Reading
	03/10/2016	Not Delivered

but some Product no has got two lines with 'Not Delivered' like this

Product no	Date	Delivery Details
S158	05/10/2016	Not Delivered
	05/10/2016	Not Delivered

for these sort of values, it is not working. the result for above should be

S158   05/10/2016   Not Delivered

Please help sunny

[sunny_talwar · Accepted solution]

You can add a Distinct in the count:

=Aggr(If(Count(DISTINCT TOTAL <[Product no]> [Delivery Details]) > 1, If(Flag = 1, [Delivery Details]), [Delivery Details]), [Product no], [Delivery Details])