How to use Let to identifyy Max value

[43918084]

I have below table that I want to identify Max (id) and the related Max (to) where 'to' should not be blank or zero

I have tried to use let but get Null value.  Would appreciate guidance on how to use Let or how to write the script to acheive my expectation

History:

LOAD

   id as [id],

to as [to]

where WildMatch(field, '*timeoriginalestimate*') or WildMatch(field, '*timeestimate*');

SELECT 

id,

 to

FROM History

WITH PROPERTIES (

issueIdOrKey='DD-1239')

;

let vMaxi=MaxString(id);

History2:
load

($'vMaxi') as id

to

resident History;

[Chanty4u · Accepted solution]

Try this 

History:

LOAD

   id as [id],

   to as [to]

WHERE WildMatch(field, '*timeoriginalestimate*') OR WildMatch(field, '*timeestimate*');

SELECT 

   id,

   to

FROM History

WITH PROPERTIES (

   issueIdOrKey='DD-1239'

);

TempMax:

LOAD 

   MaxString(id) as MaxID

RESIDENT History

WHERE Len(Trim(to)) > 0 AND to <> 0;

LET vMaxi = Peek('MaxID', 0, 'TempMax'); 

DROP TABLE TempMax;

History2:

LOAD

   id,

   to

RESIDENT History

WHERE id = '$(vMaxi)';

[JordyWegman · Accepted solution]

Hi,

I would do something like:

Table:
Load
     id as idFilter
From [YourTable.qvd]
Where to <> 0 AND to <> null()
;

Final:
Load
    field,
    id,
    Max( to ) as to
From [YourTable.qvd]
Where Exists( idFilter, id )
Group by field, id
;

Drop table Table;

Jordy

Climber

[43918084]

Thanks a million for the guidance.  I had been struggling for 2 days and you message is like magic 

[43918084 · Accepted solution]

Thanks a lot for your guidance.  I learn another option.  It is very helpful 🙂