Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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?

6 Replies

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

Not applicable

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

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.

bismart
Contributor

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.

   And link this table with your fact table.

Regards,

Kaushik Solanki

Not applicable

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 :

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?

RMAStatusnewstatus
A5 cancel4 alloc
A5 cancel1 ship
A4 alloc4 alloc
A4 alloc1 ship
A6 nonalloc4 alloc
A6 nonalloc1 ship
A7 nonalloc4 alloc
A7 nonalloc1 ship
B6 nonalloc4 alloc
B6 nonalloc1 ship
B1 ship4 alloc
B1 ship1 ship

nagaiank
Valued Contributor III

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;

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;

Community Browser