Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
The default behavior of FirstSortedValue() is to return null when two records share the same sort weight.
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
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
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
hi yes you are right id is not unique
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
;