6 Replies Latest reply: Jun 30, 2011 9:11 AM by Nagaian Krishnamoorthy

# How to use peek function to compare the cells abover or below for different groups

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.

RMAStatus
A5 cancel
A4 alloc
A6 nonalloc
A6 nonalloc
B6 nonalloc
B3 ship

I would like to see just one status per one RMA, so for example "

• for RMA 'A' one of the statuses is 4 alloc --> i would like QV to set up the status for whole RMA 'A' as 4 alloc
• for RMA 'B' one of the statuses is 3 ship --> i would like QV to set up the status for RMA 'B' as 3 ship

as below:

RMAStatusNewStatus
A5 cancel4 alloc
A4 alloc4 alloc
A6 nonalloc4 alloc
A6 nonalloc4 alloc
B6 nonalloc3 ship
B3 ship3 ship

I was trying to sort RMA & statuses and then use PEEK function but it doesent work. Any ideas how to solve it?

• ###### How to use peek function to compare the cells abover or below for different groups

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

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

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 )

• ###### How to use peek function to compare the cells abover or below for different groups

Hi,

Then the best way is to create a table in your script which will have two fields as RMA and New status.

Regards,

Kaushik Solanki

• ###### Re: How to use peek function to compare the cells abover or below for different groups

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 :

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

The following script worked for me and gave the desired result.

T1:

LOAD RMA, Text(Status) as Status, SubField(Status,' ',1) as StatusNo;

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;