Latest Non Zero Value in Script

[shiveshsingh]

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.

[martinpohl]

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

[sunny_talwar]

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

[shiveshsingh]

@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 

[sunny_talwar]

May be do this

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