Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
jduluc12
Contributor

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

Re: Disregard Dimension to show non-aggr values

Try this

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

or just this

Aggr(NODISTINCT Desc, Vendor, Number)

17 Replies
MVP
MVP

Re: Disregard Dimension to show non-aggr values

May be this?

Only(Total <Number> Desc)

Or,

Only(Total <Number, Vendor> Desc)

Re: Disregard Dimension to show non-aggr values

Or this

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

jduluc12
Contributor

Re: Disregard Dimension to show non-aggr values

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

jduluc12
Contributor

Re: Disregard Dimension to show non-aggr values

It is not working.

Re: Disregard Dimension to show non-aggr values

Does Vendor and Number repeat on all the rows?

jduluc12
Contributor

Re: Disregard Dimension to show non-aggr values

yes

vinieme12
Esteemed Contributor II

Re: Disregard Dimension to show non-aggr values

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;

Re: Disregard Dimension to show non-aggr values

Works for me

Capture.PNG

jduluc12
Contributor

Re: Disregard Dimension to show non-aggr values

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

let me try and i will update soon.