topic Re: Expression to show Last n values in QlikView

Expression to show Last n values

Anonymous — Mon, 01 Apr 2013 11:45:34 GMT

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	-

Re: Expression to show Last n values

jagan — Mon, 01 Apr 2013 12:35:27 GMT

HI,

In load script try like this

LOAD
result,

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

FROM DataSource;

Regards,

Jagan.

Re: Expression to show Last n values

Gysbert_Wassenaar — Mon, 01 Apr 2013 13:56:50 GMT

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

Re: Expression to show Last n values

Anonymous — Mon, 01 Apr 2013 14:12:44 GMT

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

Re: Expression to show Last n values

Anonymous — Mon, 01 Apr 2013 14:40:56 GMT

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

Re: Expression to show Last n values

Anonymous — Mon, 01 Apr 2013 17:20:33 GMT

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

Re: Expression to show Last n values

Anonymous — Mon, 01 Apr 2013 18:42:00 GMT

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

Re: Expression to show Last n values

Gysbert_Wassenaar — Tue, 02 Apr 2013 08:40:30 GMT

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.

Re: Expression to show Last n values

Anonymous — Wed, 03 Apr 2013 06:15:15 GMT

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