topic Re: need help in expression in QlikView

need help in expression

Wed, 16 Apr 2014 12:16:49 GMT

For selected date if the values is not available then get previous nearest date value.

How to achieve this?

Reards,

Prajna

Re: need help in expression

tresB — Wed, 16 Apr 2014 12:23:48 GMT

You can get that using peek() in the script. Have a look here: Generating Missing Data In QlikView

Re: need help in expression

MK_QSL — Wed, 16 Apr 2014 12:25:33 GMT

Use of Previous/Peek function you can replace the Null Data with previous one.

If you share your sample file or apps, we can help you in better way.

Re: need help in expression

Wed, 16 Apr 2014 12:31:36 GMT

well the requirement is i need to show balance or that particular day.if there is no balance available the i should consider previous day balance.

Say apr 14 we have balance ,apr 15 we have,ap 18 we have

so now apr 16 and 17 should have 15 data(balance)

balance needs to be carried forwad.

Regards,

Prajna

Re: need help in expression

MK_QSL — Wed, 16 Apr 2014 12:37:09 GMT

Sample Script enclosed...

======

TempTable:

LOAD Item, Date, IF(Len(Trim(Balance))=0,Null(),Balance) as Balance Inline

[

Item, Date, Balance

A, 01/10/2010, 100

A, 02/10/2010, 10

A, 03/10/2010,

B, 24/10/2010, 150

B, 25/10/2010,

B, 26/10/2010, 80

];

Table:

NoConcatenate

Load

If(IsNull(Item),Peek(Item),Item) as Item,

Date,

If(IsNull(Balance), Peek(Balance), Balance) as Balance

Resident TempTable

Order By Date;

Drop Table TempTable;

=======

Re: need help in expression

rajat2392 — Wed, 16 Apr 2014 12:44:39 GMT

A little modification needed in Manish's script

====

TempTable:

LOAD Item, Date, IF(Len(Trim(Balance))=0,Null(),Balance) as Balance Inline

[

Item, Date, Balance

A, 01/10/2010, 100

A, 02/10/2010, 10

A, 03/10/2010,

B, 24/10/2010, 150

B, 25/10/2010,

B, 26/10/2010, 80

];

Table:

NoConcatenate

Load

If(IsNull(Item),Peek(Item),Item) as Item,

Date,

If(IsNull(Balance),If(Item=Peek(Item),Peek(Balance),0), Balance) as Balance

Resident TempTable

Order By Date;

Drop Table TempTable;

=======

Else if the first row of customer B has no Balance value, it will take the previous balance value which is for A, so it will go wrong.