Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Peek on first line (Even if Null)

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

CommImage.png

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

15 Replies
vishsaggi
Esteemed Contributor III

Re: Peek on first line (Even if Null)

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.

Capture.PNG

Not applicable

Re: Peek on first line (Even if Null)

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
Esteemed Contributor III

Re: Peek on first line (Even if Null)

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?

Not applicable

Re: Peek on first line (Even if Null)

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
Esteemed Contributor III

Re: Peek on first line (Even if Null)

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

vishsaggi
Esteemed Contributor III

Re: Peek on first line (Even if Null)

Try this in the expression?

Dim: Adimension, Date, Value

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

Capture.PNG

Not applicable

Re: Peek on first line (Even if Null)

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)

Are you looking to do this in the script?

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

Not applicable

Re: Peek on first line (Even if Null)

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.