Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to prepare report where i have for RMA more statuses.
For RMA 'A' I have statuses cancel. alloc and 2times nonalloc for RMA 'B' nonalloc & ship.
RMA | Status |
---|---|
A | 5 cancel |
A | 4 alloc |
A | 6 nonalloc |
A | 6 nonalloc |
B | 6 nonalloc |
B | 3 ship |
I would like to see just one status per one RMA, so for example "
as below:
RMA | Status | NewStatus |
---|---|---|
A | 5 cancel | 4 alloc |
A | 4 alloc | 4 alloc |
A | 6 nonalloc | 4 alloc |
A | 6 nonalloc | 4 alloc |
B | 6 nonalloc | 3 ship |
B | 3 ship | 3 ship |
I was trying to sort RMA & statuses and then use PEEK function but it doesent work. Any ideas how to solve it?
HI,
You can use a calculated dimension in straight table.
You can create calculated dimension using if statement. Like this
=if(RMA = 'A','4 alloc',
if(RMA = 'B','3 ship'))
Regards,
Kaushik Solanki
Thank you for your answer.
I cannot use calculated dimension as I load about 20 000 RMA's in my original file and all of them will have different qty of statuses in different combinations. i gave each status a number (1... is most important) and i want QV to show per RMA just the most important status.
Have a look at FirstSortedvalue...
firstsortedvalue( [{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression [, sort_weight [, n]])
returns the first value of expression sorted by corresponding sort-weight when expression is iterated over the chart dimension(s). Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return null. By stating an n larger than 1, you will get the nth value in order.
Examples:
firstsortedvalue ( PurchasedArticle, OrderDate )
firstsortedvalue ( PurchasedArticle, -OrderDate, 2 )
firstsortedvalue ( A/B, X*Y/3 )
firstsortedvalue ( distinct PurchasedArticle, OrderDate )
firstsortedvalue ( total PurchasedArticle, OrderDate )
firstsortedvalue ( total <Grp> PurchasedArticle, OrderDate )
Hi,
Then the best way is to create a table in your script which will have two fields as RMA and New status.
And link this table with your fact table.
Regards,
Kaushik Solanki
I found that in chart I can use calculated dimension AGGR (MinString (Status), RMA) and it works perfectly but AGGR function cannot be used in script. In script i used :
LOAD
MinString(Status) as newstatus
RESIDENT RawData
Group by RMA;
This in place of showing for RMA 'A' just 4 alloc and for RMA'B' just 1 ship it shows for each position in table and 4 alloc and 1 ship as below. How to solve it?
RMA | Status | newstatus |
A | 5 cancel | 4 alloc |
A | 5 cancel | 1 ship |
A | 4 alloc | 4 alloc |
A | 4 alloc | 1 ship |
A | 6 nonalloc | 4 alloc |
A | 6 nonalloc | 1 ship |
A | 7 nonalloc | 4 alloc |
A | 7 nonalloc | 1 ship |
B | 6 nonalloc | 4 alloc |
B | 6 nonalloc | 1 ship |
B | 1 ship | 4 alloc |
B | 1 ship | 1 ship |
The following script worked for me and gave the desired result.
T1:
LOAD RMA, Text(Status) as Status, SubField(Status,' ',1) as StatusNo;
LOAD * Inline [
RMA,Status
A,5 cancel
A,4 alloc
A,6 nonalloc
A,6 nonalloc
B,6 nonalloc
B,3 ship
];
T2:
NoConcatenate
LOAD RMA, FirstSortedValue(Status, StatusNo) as NewStatus Resident T1 Group By RMA;