topic Re: Latest Non Zero Value in Script in QlikView

Latest Non Zero Value in Script

shiveshsingh — Wed, 05 Jun 2019 11:17:45 GMT

Hi All

Please refer below sample data set.

Date	Value
01-06-2019	45
02-06-2019	12
03-06-2019	100
04-06-2019	0
05-06-2019	10
06-06-2019	5
07-06-2019	0
08-06-2019	0
09-06-2019	10

Requirement is, If i run script on 7June, then 5 ( Latest Non Zero Value) should be stored(In script Only).

If I run the code on 8th, only 5 should come but when i run code on 9th, that 5 should be overridden by 10.

Please help for the code in script.

Re: Latest Non Zero Value in Script

martinpohl — Wed, 05 Jun 2019 11:27:05 GMT

Hi,

for this load

Max:

max(Date) as MaxDate

resident YourData

where Value > 0;

left join load

Date as MaxDate

Value

resident YourData;

drop table YourData;

Regards

Re: Latest Non Zero Value in Script

sunny_talwar — Wed, 05 Jun 2019 12:41:12 GMT

Where exactly are you looking to store this? In a variable or a table?

Re: Latest Non Zero Value in Script

shiveshsingh — Wed, 05 Jun 2019 12:54:09 GMT

@sunny_talwar

I have a table with fields [Extraction Date],

[Date type],
[Object type],
[Object type sort order],
[Data / Valueset / Object],
[Domain / System],
[Table],
[Process Area],
[System sort order],
Client,
[# of correct records],
[# not correct records],
[# of records]

I just want to store all the latest value in a table where [# of records] is not zero

Re: Latest Non Zero Value in Script

sunny_talwar — Wed, 05 Jun 2019 13:35:06 GMT

May be do this

Right Join (TableNameHere) LOAD Max([Extraction Date]) as [Extraction Date] Resident TableNameHere Where [# of records] <> 0;