Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Badzreyes00
Contributor III
Contributor III

Get the value based on Min Date

Hi All,

Hope you are all doing well, I hope you can help me with my dilemma below,

my desired output is the "REMARKS" column. I am having problem doing this in IF condition since I need also to check if there is another PLACE value. 

Person No.PlaceDateRemarks
1001Home8/20/2018Home first, then work
1001Work1/10/2019Home first, then work
1001Work1/12/2019Home first, then work
1002Home8/10/2019Home Only
1003Work9/20/2019Work Only
1004Work9/20/2019Work first, then Home
1004Home9/21/2019Work first, then Home
1004Work9/23/2019Work first, then Home
1005Home9/25/2019Work first, then Home
1005Work9/23/2019Work first, then Home
1005Work9/24/2019Work first, then Home
1006Home10/10/2019Home Only

 

The syntax that I am using is 

if
(
If(Aggr(NODISTINCT Min(Date), Person_No) = Date, Place)='Home','Home Only',
if
(
If(Aggr(NODISTINCT Min(Date), Person_No) = Date, Place)='Work','Work Only''
))

I cannot figure out how can I check the 2nd value for for Place column so that I can input the condition for "Work first, then Home" or "Home first, then Home"

I would really appreciate the help, thank you so much in advance!

Labels (6)
1 Solution

Accepted Solutions
rubenmarin

Hi it's intended to work as expression. To make it a dimension you need to enclose the expression in an aggr():

Aggr(Expression, [Person No.])

Aggr(If(Count(DISTINCT TOTAL <[Person No.]> Place)=1
  ,If(Place='Home', 'Only home', 'Only Work')
  ,If(FirstSortedValue(TOTAL <[Person No.]> Place, Date)='Home', 'First Home then Work', 'First Work then Home'
)), [Person No.])

View solution in original post

6 Replies
rubenmarin

Hi, can you check if this expression works?:

 

If(Count(DISTINCT TOTAL <[Person No.]> Place)=1
  ,If(Place='Home', 'Only home', 'Only Work')
  ,If(FirstSortedValue(TOTAL <[Person No.]> Place, Date)='Home', 'First Home then Work', 'First Work then Home'
))

 

Badzreyes00
Contributor III
Contributor III
Author

Hi Ruben, 

Thank you so much for your response. It is "OK" in script editor but after applying I encountered an "Invalid Dimension" Error :(((

Badzreyes00
Contributor III
Contributor III
Author

I tried changing those fields with < > into , or adding {}. but it only giving me the Else value to all 😞

rubenmarin

Hi it's intended to work as expression. To make it a dimension you need to enclose the expression in an aggr():

Aggr(Expression, [Person No.])

Aggr(If(Count(DISTINCT TOTAL <[Person No.]> Place)=1
  ,If(Place='Home', 'Only home', 'Only Work')
  ,If(FirstSortedValue(TOTAL <[Person No.]> Place, Date)='Home', 'First Home then Work', 'First Work then Home'
)), [Person No.])
Badzreyes00
Contributor III
Contributor III
Author

Hi Ruben,

It works on the first set of expression(Home only and work only) but does not work for home first then work and work first then home. The output it like below:

PlacePerson_noDateRemark
Home10000010105/23/2019First Work then Home
Work10000010105/30/2019First Work then Home
Work10000010106/07/2019First Work then Home
Work10000010106/13/2019First Work then Home

 

I tried tweaking if but failed, I appreciate your help hope you can help me with this one. Thank you!

rubenmarin

I don't know, seems working for me. Sample attached.