
How to use peek function to compare the cells abover or below for different groups
Kaushik Solanki Jun 30, 2011 3:56 AM (in response to Anna Kornatowska)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

How to use peek function to compare the cells abover or below for different groups
Anna Kornatowska Jun 30, 2011 4:06 AM (in response to 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.

How to use peek function to compare the cells abover or below for different groups
Dave Finlay Jun 30, 2011 4:21 AM (in response to Anna Kornatowska)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 sortweight when expression is iterated over the chart dimension(s). Sortweight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sortvalue expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sortorder, 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 )

How to use peek function to compare the cells abover or below for different groups
Kaushik Solanki Jun 30, 2011 5:36 AM (in response to Anna Kornatowska)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



Re: How to use peek function to compare the cells abover or below for different groups
Anna Kornatowska Jun 30, 2011 7:56 AM (in response to Anna Kornatowska)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 
Re: How to use peek function to compare the cells abover or below for different groups
Nagaian Krishnamoorthy Jun 30, 2011 9:11 AM (in response to Anna Kornatowska)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;
