Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
43918084
Creator II
Creator II

How to use Let to identifyy Max value

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

43918084_0-1738061420615.png

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;

 

 

 

 

Labels (4)
3 Solutions

Accepted Solutions
Chanty4u
MVP
MVP

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)';

View solution in original post

JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder

View solution in original post

43918084
Creator II
Creator II
Author

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

View solution in original post

4 Replies
Chanty4u
MVP
MVP

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
Partner - Master
Partner - Master

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

Work smarter, not harder
43918084
Creator II
Creator II
Author

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

43918084
Creator II
Creator II
Author

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