Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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
Creator
Creator

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 )    

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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
Specialist III
Specialist III

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;