Help with qlik code to get 2 records if matching dates are same

[sai_12]

Hi everyone, i have using the following code to get which service was first given based on the date,

but if 2 services are given on same date i am getting null as output , i would like to have both those records if same service date 

My code

by_first_month:
Load
id as id,
min(date(Service_dt)) as service_dt,
FirstSortedValue (Service_Group, Service_dt) as Service_Group_test,
FirstSortedValue (icode, Service_dt) as icode_test,
FirstSortedValue (amount, Service_dt) as amount_test,
resident [table]
Where Service_Group='test1 'or Service_Group='test 2 'or Service_Group='test3'or Service_Group='test4'
group by id;

In the above code i am filtering the records from table , where service_group is in test1 ,test 2 ,test3,test4

then i am finding the minimum of service date , and first sorted value based of other dimensions based on 

My aim is to get only records where we have min service date 

The problem is when 2 service_groups have same service date i am getting null, as output , 

any help will be appreciated Thanks

[rwunderlich]

The default behavior of FirstSortedValue() is to return null when two records share the same sort weight. 

https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/AggregationFunc...

You can change this by adding the DISTINCT keyword to the function:

FirstSortedValue (DISTINCT Service_Group, Service_dt) as Service_Group_test

However, you said you wanted both records, so distinct won't give you want you want. Instead use a Join like this:

Inner Join(table) Load
id as id,
min(date(Service_dt)) as Service_dt,
resident [table]
Where Service_Group='test1 'or Service_Group='test 2 'or Service_Group='test3'or Service_Group='test4'
group by id;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[marksouzacosta]

I'm curious about the Id field. Is that unique or do you have repeated values? I think it is not unique, but I'm just confirming.

One quick hint: you can rewrite your where clause to this:

Where Match(Service_Group,'test1 ','test 2 ','test3','test4')

Regards,

Mark Costa 

[sai_12]

Hi tried this solution but it wont work as in the resident table i have other service groups also eg:test5 test6 etc so when i use the above i get all other service groups associated with min service

[sai_12]

hi yes you are right id is not unique

[marksouzacosta]

What if you include Service Group in @rwunderlich script?

Inner Join(table) 
Load
   id as id,
   Service_Group,
   min(date(Service_dt)) as Service_dt,
resident
   [table]
Where
   Match(Service_Group,'test1 ','test 2 ','test3','test4')
group by 
   id, 
   Service_Group
;