Peek on first line (Even if Null)

[#1]

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

[vishsaggi]

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.

[#3]

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

[vishsaggi]

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?

[#5]

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

[vishsaggi]

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

[vishsaggi]

Try this in the expression?

Dim: Adimension, Date, Value

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

[#8]

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

[sunny_talwar]

Are you looking to do this in the script?

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

[#10]

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.