Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jduluc12
Creator
Creator

Disregard Dimension to show non-aggr values

Hi,

My data set looks like this

VendorNumberItemDescAmount
Vendor_1123For ABC
Vendor_1123Item-1100
Vendor_1123Item-2200
Vendor_3456For DEF
Vendor_3456Item-2200

And I need to build a pivot table like this

Dimension:

Vendor

Number

Item

Expression:

sum(Amount)

Desc

As you see that Desc is not available on the same row where Item is so it shows up empty.

Is there any way to show Desc for each Item on the row

Like as follows?

VendorNumberItemAmountDesc
Vendor_1123Item-1100For ABC
Item-2200For ABC
Vendor_3456Item-2200For DEF

I tried using Only function with Total but it does work.

Thanks,

Jean

1 Solution

Accepted Solutions
sunny_talwar

Try this

If(Sum(Amount) > 0, Aggr(NODISTINCT Desc, Vendor, Number))

or just this

Aggr(NODISTINCT Desc, Vendor, Number)

View solution in original post

17 Replies
tresesco
MVP
MVP

May be this?

Only(Total <Number> Desc)

Or,

Only(Total <Number, Vendor> Desc)

sunny_talwar

Or this

If(Sum(Amount) > 0, Only(TOTAL <Vendor, Number> Desc))

jduluc12
Creator
Creator
Author

I tried exactly this before i asked here but it does not work.

jduluc12
Creator
Creator
Author

It is not working.

sunny_talwar

Does Vendor and Number repeat on all the rows?

jduluc12
Creator
Creator
Author

yes

vinieme12
Champion III
Champion III

While importing the data fill down the values

example

temp:

Load

if(isnull(Vendor,peek(Vendor),Vendor) as Vendor,

if(isnull(Item,peek(Item),Item) as Item,

. <<repeat for other columns except Amount>>

.

.

From XXXX;


NOCONCATENATE


FACT:

load * from temp

where NOT Isnull(Amount);



Drop table temp;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunny_talwar

Works for me

Capture.PNG

jduluc12
Creator
Creator
Author

I think i know why is it not working for me.

let me try and i will update soon.