Expression to show Last n values

[Anonymous]

Hi

I have a column 'result' that can contain a 1 or 2 digit number or a single letter.  What I want is to create an expression, output, that shows the last 6 results separated by comma.  If result >= 10 then 0 should be used in the output.

The table shows the desired results but despite playing about with various functions I've not been able to achieve this.  Any suggestions are most welcome

Thanks

Matt

result	output
3	5,0,F,P,8,2
5	0,F,P,8,2,6
11	F,P,8,2,6,4
F	P,8,2,6,4,1
P	8,2,6,4,1
8	2,6,4,1
2	6,4,1
6	4,1
4	1
1	-

[jagan]

HI,

In load script try like this

LOAD
     result,

     if(result >=10, 0, output) AS output

FROM DataSource;

Regards,

Jagan.

[Gysbert_Wassenaar]

See attached qvw. I use two variables to build up this expression:

     $(vConcat(1,2)) & $(vConcat(2,3)) & $(vConcat(3,4)) & $(vConcat(4,5)) &

     $(vConcat(5,6)) & if(len(trim($(vItem(6))))>0,$(vItem(6)))

Variables:

     vItem:

      below(if(num(result) >=10,0,result),$1)

This checks if the value of result is a number >=10. The below function is used with parameter $1 to extract the value from x lines below

     vConcat:

     if(len(trim($(vItem($1))))>0, if(len(trim($(vItem($2))))>0,$(vItem($1)) &',', $(vItem($1))))

This checks for nulls so no extra comma's are added

[Anonymous]

Hi Jagan

Thanks for the suggestion.  I use the following to load the datasource:

SQL SELECT *

FROM "database".dbo."table";

How can I incorporate the LOAD statement into this?

Thanks

Matt

[Anonymous]

Thankyou Gysbert, I am running Qlikview Personal Edition so cannot view your file but I like the idea of your solution.

I have created the two variables and the expression but unfortunately it is returning null values.  I'm double checking to make sure I've not made any errors.

Thanks

Matt

[Anonymous]

I double checked everything and can't see any errors.  Is there anything I can check to see why I'm getting null values?

Thanks

Matt

[Anonymous]

Hi Gysbert

I'm wondering if I'm seeing null values because of the use of num in the variable?

below(if(num(result) >=10,0,result),$1)

The result field can contain a letter as I stated in the original post.  I tried changing num to text but the output was still nulls so perhaps this is not the reason but thought I'd mention it.

Thanks

Matt

[Gysbert_Wassenaar]

It's not the num. That's only to be able to compare the value with 10 only if it's a number. Is the name of your field really 'result'? Qlikview is case-sensitive so Result is not the same as result or RESULT.

[Anonymous]

The field name is actual_result.  I've amended this in the variable vItem and the case matches.