topic Re: calculated dimension in QlikView

calculated dimension

Wed, 25 Nov 2020 16:16:04 GMT

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
S125	Not Delivered
S126	Not Delivered
S126	Delivered at Birmingham
S130	Not Delivered
S140	Delivered at Reading
S140	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

Re: calculated dimension

sunny_talwar — Thu, 06 Oct 2016 11:07:07 GMT

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])

Re: calculated dimension

Thu, 06 Oct 2016 11:22:06 GMT

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
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

Re: calculated dimension

jyothish8807 — Thu, 06 Oct 2016 11:28:00 GMT

Hi Rajendran,

Try to create a calculated dimension:

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

Regards

Re: calculated dimension

Thu, 06 Oct 2016 11:37:16 GMT

No its not working

Re: calculated dimension

sunny_talwar — Thu, 06 Oct 2016 11:40:29 GMT

Check out the attached

Re: calculated dimension

jyothish8807 — Thu, 06 Oct 2016 11:40:51 GMT

Hi Rajendran,

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

Regards

Re: calculated dimension

sunny_talwar — Thu, 06 Oct 2016 11:44:29 GMT

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];

Re: calculated dimension

Thu, 06 Oct 2016 12:27:26 GMT

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
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

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

Product no	Date	Delivery Details
S158	05/10/2016	Not Delivered
S158	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

Re: calculated dimension

sunny_talwar — Thu, 06 Oct 2016 12:36:49 GMT

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])

Re: calculated dimension

effinty2112 — Thu, 06 Oct 2016 12:50:48 GMT

You could try:

Set a variable to keep things tidy:

vDelivered = if(WildMatch([Delivery Details],'*Delivered at*'),1,0)

Then use this expression:

if(sum($(vDelivered)) = 0, 'Not delivered', concat(if($(vDelivered) = 1,[Delivery Details])))

giving this table:

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

Regards

Andrew

Re: calculated dimension

Mon, 10 Oct 2016 09:39:37 GMT

Thanks to all