Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
let vMaxi=MaxString(id);
History2:
load
($'vMaxi') as id
to
resident History;
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)';
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
Thanks a lot for your guidance. I learn another option. It is very helpful 🙂
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)';
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
Thanks a million for the guidance. I had been struggling for 2 days and you message is like magic
Thanks a lot for your guidance. I learn another option. It is very helpful 🙂