topic Re: Disregard Dimension to show non-aggr values in QlikView

Disregard Dimension to show non-aggr values

jduluc12 — Tue, 24 Oct 2017 12:37:03 GMT

Hi,

My data set looks like this

Vendor	Number	Item	Desc	Amount
Vendor_1	123		For ABC
Vendor_1	123	Item-1		100
Vendor_1	123	Item-2		200
Vendor_3	456		For DEF
Vendor_3	456	Item-2		200

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?

Vendor	Number	Item	Amount	Desc
Vendor_1	123	Item-1	100	For ABC
		Item-2	200	For ABC
Vendor_3	456	Item-2	200	For DEF

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

Thanks,

Jean

Re: Disregard Dimension to show non-aggr values

tresB — Tue, 24 Oct 2017 12:42:23 GMT

May be this?

Only(Total <Number> Desc)

Or,

Only(Total <Number, Vendor> Desc)

Re: Disregard Dimension to show non-aggr values

sunny_talwar — Tue, 24 Oct 2017 12:46:33 GMT

Or this

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

Re: Disregard Dimension to show non-aggr values

jduluc12 — Tue, 24 Oct 2017 12:49:29 GMT

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

Re: Disregard Dimension to show non-aggr values

jduluc12 — Tue, 24 Oct 2017 12:51:29 GMT

It is not working.

Re: Disregard Dimension to show non-aggr values

sunny_talwar — Tue, 24 Oct 2017 12:54:05 GMT

Does Vendor and Number repeat on all the rows?

Re: Disregard Dimension to show non-aggr values

jduluc12 — Tue, 24 Oct 2017 12:55:51 GMT

yes

Re: Disregard Dimension to show non-aggr values

vinieme12 — Tue, 24 Oct 2017 13:05:23 GMT

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

sunny_talwar — Tue, 24 Oct 2017 13:07:18 GMT

Works for me

Re: Disregard Dimension to show non-aggr values

jduluc12 — Tue, 24 Oct 2017 13:10:30 GMT

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

let me try and i will update soon.

Re: Disregard Dimension to show non-aggr values

jduluc12 — Tue, 24 Oct 2017 13:10:55 GMT

I can not change the load script

Re: Disregard Dimension to show non-aggr values

jduluc12 — Tue, 24 Oct 2017 13:12:48 GMT

It does not for me when I check "Suppress when value is null" for item.

I need to select that option for Items

Re: Disregard Dimension to show non-aggr values

sunny_talwar — Tue, 24 Oct 2017 13:14:06 GMT

Can you update my sample to show what exactly are you doing?

Re: Disregard Dimension to show non-aggr values

jduluc12 — Tue, 24 Oct 2017 13:23:00 GMT

Just check the "Suppress When Value is Null " for item and it stops showing the value for the desc.

I just tried in your sample.

Re: Disregard Dimension to show non-aggr values

sunny_talwar — Tue, 24 Oct 2017 13:25:47 GMT

Try this

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

or just this

Aggr(NODISTINCT Desc, Vendor, Number)

Re: Disregard Dimension to show non-aggr values

jduluc12 — Tue, 24 Oct 2017 13:25:58 GMT

Here is the changed file.

Re: Disregard Dimension to show non-aggr values

jduluc12 — Tue, 24 Oct 2017 13:27:33 GMT

Thanks That works!

You are awesome!!

Re: Disregard Dimension to show non-aggr values

sunny_talwar — Tue, 24 Oct 2017 13:27:40 GMT

Updated it... check attached