topic Re: Peek on first line (Even if Null) in QlikView

Peek on first line (Even if Null)

Thu, 25 May 2017 15:15:18 GMT

Hi all,

Thanks for looking and helping with this challenge,

I've been trying to get a Peek expression to place the first result on the first line with no success,

Essentially, with the attached data set I've added in the load script the following line ->

Peek(Value) * If(ISNULL(Peek(list)), 100, (Peek(list))) as list

Which creates exactly what I want (Essentially a workaround for the lack of Excel function Product) however the part I can't figure out is how to get the first Peek result on the first line, so for example

I need the 102.1751 below the null to be on the line above it next to 102% and the same for all the rest?

Any ideas please?

Many thanks

Re: Peek on first line (Even if Null)

vishsaggi — Thu, 25 May 2017 15:29:40 GMT

When i ran your Excel sheet inside qlikview i cant find any nulls, can you send me a screen shot of where you displaying this information in Qlikview which has Null?

Table1:

LOAD Adimension,

Date,

Value,

Peek(Value) * If(ISNULL(Peek(list)),100, (Peek(list))) as list

FROM

[..\..\DataforComm2.xlsx]

(ooxml, embedded labels, table is Sheet1);

This is what i got when used Straight table chart.

Re: Peek on first line (Even if Null)

Thu, 25 May 2017 15:33:34 GMT

Hi Vish,

Thanks for the reply, The issue is that as per your image you're starting on the 26th Feb 1999 when the data actually starts in Jan 1999, so I need the 102.18 to be next to the Jan 1999 Record that it takes the initial Peek at.

Does that make sense, as per my image above (which I'll admit I used Sense to quick test this in) shows the line 20/01/1999 with a null/missing value in the List field.

Hope that all makes sense

MAny thanks

Ryan

Re: Peek on first line (Even if Null)

vishsaggi — Thu, 25 May 2017 15:55:47 GMT

But excel sheet has a value 102 for 29/01/2017. There are no nulls. Even though i don't use your peek script line. So can you update your excel sheet and re post it if possible?

Re: Peek on first line (Even if Null)

Thu, 25 May 2017 15:59:49 GMT

Ok Null may be the wrong word, apologies.

the Field List doesnt exist until you use that expression which then creates it

As per the image I posted above you can see that on the line representing Jan 1999 List shows a - / blank / null and the 102.175 is represented on the line below it, feb 1999. That 102.175 needs to be on the same line has Jan 1999.

Does that make sense?

Many thanks

Ryan

Re: Peek on first line (Even if Null)

vishsaggi — Thu, 25 May 2017 16:27:17 GMT

Can i know the whole purpose of creating field "list" in this?

Re: Peek on first line (Even if Null)

vishsaggi — Thu, 25 May 2017 16:52:03 GMT

Try this in the expression?

Dim: Adimension, Date, Value

Expr: = IF(Sum(list) = 0.00, Below(TOTAL sum(list)), sum(list))

Re: Peek on first line (Even if Null)

Fri, 26 May 2017 11:56:16 GMT

Hi Vish,

Cheers for the update, unfortunatly whilst that does get 102.18 onto the firstline, the rest of the lines are still incorrect, they all need to come up one line.

MAny thanks

Ryan

Re: Peek on first line (Even if Null)

sunny_talwar — Fri, 26 May 2017 12:09:06 GMT

Are you looking to do this in the script?

Peek(Value) * If(ISNULL(Peek(list)), 100, (Peek(list))) as list

Re: Peek on first line (Even if Null)

Fri, 26 May 2017 12:11:54 GMT

Front end or back end I don't mind, I've ordered the data set its loading off of to be in the right order when it comes in (Product and then Date) to make it as easy as possible. To be fair, the above does work its just the Peek inputs the result into the row below the one it Peeked at which is frustrating.

Re: Peek on first line (Even if Null)

sunny_talwar — Fri, 26 May 2017 12:14:11 GMT

I don't really have the data to try this out... but may be you need this

Below(TOTAL Sum(list))

Below(Sum(list))

Re: Peek on first line (Even if Null)

Fri, 26 May 2017 12:16:40 GMT

I attached some data to the original post if that helps, but I'll give that a go now, cheers

Re: Peek on first line (Even if Null)

Fri, 26 May 2017 12:19:45 GMT

This works with just one unfortunate flaw, because the Peek is one row down, it means the latest date is therefore missing when you use Below to shuffle them one row back up.

Re: Peek on first line (Even if Null)

sunny_talwar — Fri, 26 May 2017 12:40:25 GMT

What would you want to see for the latest date? I am confused regarding the required output

Re: Peek on first line (Even if Null)

Fri, 26 May 2017 12:48:46 GMT

Hi Sunny,

Appreciate the need for clarification, so please see attached,

This will show you how its worked out in Excel, unfortunatly I don't have Product function in Qlik (As much as that would solve near enough all my questions) so I've had to work around that in a variety of ways.

In the price column you'll see that March should be 224.67, and next to it on the right (and below) the sums I'm working towards building once these calculations are complete.

Hope this helps?

Many thanks

Ryan

Re: Peek on first line (Even if Null)

sunny_talwar — Fri, 26 May 2017 12:54:57 GMT

Try this script may be

Table:

LOAD F2 as Date,

"Monthly return"

FROM [lib://Lib/Copy of Book2.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

1 + [Monthly return] as [1+MR],

Alt(Peek('Price') * (1 + [Monthly return]), 100) as Price

Resident Table

Order By Date;

DROP Table Table;