Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this
If(Sum(Amount) > 0, Aggr(NODISTINCT Desc, Vendor, Number))
or just this
Aggr(NODISTINCT Desc, Vendor, Number)
May be this?
Only(Total <Number> Desc)
Or,
Only(Total <Number, Vendor> Desc)
Or this
If(Sum(Amount) > 0, Only(TOTAL <Vendor, Number> Desc))
I tried exactly this before i asked here but it does not work.
It is not working.
Does Vendor and Number repeat on all the rows?
yes
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;
Works for me
I think i know why is it not working for me.
let me try and i will update soon.