Solved: Re: Select distinct question.. - Qlik Community

juarez_chu · ‎2015-12-21

Hi,

Im new to qlikview, just want to ask question on how to use distinct. I am trying to load the distinct values on Incident No. and latest action_timestamp. The condition is if new_state is Config_Testing, then that distinct Incident will be selected. If there is no Config_Testing for that Incident No., the latest timestamp will be selected. For below example, records 5, 7, and 10 will be loaded only. Can you help how can I achieve this? Thank you.

Incident No.	new_state	action_timestamp
PHD200341954	Delivered	Dec 4, 2015 5:17:55 PM
PHD200341954	Submitted	Nov 24, 2015 1:04:26 PM
PHD200341954	Config_Testing	Dec 1, 2015 3:28:21 PM
PHD200341954	SIT	Dec 1, 2015 3:43:57 PM
PHD200341954	Config_Testing	Dec 1, 2015 3:44:08 PM
PHD200341954	Submitted	Nov 24, 2015 2:22:36 PM
PHD200341944	Submitted	Dec 4, 2015 5:17:55 PM
PHD200341944	Submitted	Nov 24, 2015 1:04:26 PM
PHD200341944	Submitted	Dec 1, 2015 3:28:21 PM
PHD200341952	Config_Testing	Dec 4, 2015 5:17:55 PM
PHD200341952	Submitted	Nov 24, 2015 1:04:26 PM
PHD200341952	Config_Testing	Dec 1, 2015 3:28:21 PM
PHD200341952	SIT	Dec 1, 2015 3:43:57 PM
PHD200341952	Config_Testing	Dec 1, 2015 3:44:08 PM
PHD200341952	Submitted	Nov 24, 2015 2:22:36 PM

tresesco · ‎2015-12-22

Data:
LOAD *

INLINE [
Incident No, new_state, action_timestamp
PHD200341954, Delivered, '04/12/2015 5:17:55 PM'
PHD200341954, Submitted, '24/11/2015 1:04:26 PM'
PHD200341954, Config_Testing, '01/12/2015 3:28:21 PM'
PHD200341954, SIT, '01/12/2015 3:43:57 PM'
PHD200341954, Config_Testing, '01/12/2015 3:44:08 PM'
PHD200341954, Submitted, '24/11/2015 2:22:36 PM'
PHD200341944, Submitted, '4/12/2015 5:17:55 PM'
PHD200341944, Submitted, '24/11/2015 1:04:26 PM'
PHD200341944, Submitted, '1/12/2015 3:28:21 PM'
PHD200341952, Config_Testing, '4/12/2015 5:17:55 PM'
PHD200341952, Submitted, '24/11/2015 1:04:26 PM'
PHD200341952, Config_Testing,' 1/12/2015 3:28:21 PM'
PHD200341952, SIT, '1/12/2015 3:43:57 PM'
PHD200341952, Config_Testing,' 1/12/2015 3:44:08 PM'
PHD200341952, Submitted, '24/11/2015 2:22:36 PM'

];

NoConcatenate
Tab:
Load
"Incident No",
If( IsNull(Max(if(new_state='Config_Testing', action_timestamp))), max(action_timestamp),
   Max(if(new_state='Config_Testing', action_timestamp)))    AS action_timestamp,

If( IsNull(Max(if(new_state='Config_Testing', action_timestamp))), FirstSortedValue(new_state,-action_timestamp),
   FirstSortedValue(new_state,-if(new_state='Config_Testing', action_timestamp)))    AS new_state

Resident Data Group By "Incident No";

Drop table Data;

My previous post was a front-end solution. Now I notice, you asked for a script solution, so I provided.

View solution in original post

Chanty4u · ‎2015-12-22

=Count( Distinct [Fieldname])

juarez_chu · ‎2015-12-22

Thanks sureshqv. But I would like to use LOAD statement, and how I can achieve the condition: If there is no Config_Testing for that Incident No., the latest timestamp will be selected?

HirisH_V7 · ‎2015-12-22

Hi,

you can acheive like this,

Backend:

Data:

LOAD * ,

IF(new_state='Config_Testing', [Incident No]) as condition1,

IF(new_state<>'Config_Testing', Timestamp(action_timestamp,'DD/MM/YYYY')) as condition2

INLINE [

Incident No, new_state, action_timestamp

PHD200341954, Delivered, '04/12/2015 5:17:55 PM'

PHD200341954, Submitted, '24/11/2015 1:04:26 PM'

PHD200341954, Config_Testing, '01/12/2015 3:28:21 PM'

PHD200341954, SIT, '01/12/2015 3:43:57 PM'

PHD200341954, Config_Testing, '01/12/2015 3:44:08 PM'

PHD200341954, Submitted, '24/11/2015 2:22:36 PM'

PHD200341944, Submitted, '4/12/2015 5:17:55 PM'

PHD200341944, Submitted, '24/11/2015 1:04:26 PM'

PHD200341944, Submitted, '1/12/2015 3:28:21 PM'

PHD200341952, Config_Testing, '4/12/2015 5:17:55 PM'

PHD200341952, Submitted, '24/11/2015 1:04:26 PM'

PHD200341952, Config_Testing,' 1/12/2015 3:28:21 PM'

PHD200341952, SIT, '1/12/2015 3:43:57 PM'

PHD200341952, Config_Testing,' 1/12/2015 3:44:08 PM'

PHD200341952, Submitted, '24/11/2015 2:22:36 PM'

];

Front End Output:

Hope this is what you required,

PFA,

Regards,

Hirish.

HirisH
“Aspire to Inspire before we Expire!”

juarez_chu · ‎2015-12-22

Hi Hirish, thank you but my output should be like something below. Only the unique Incident No., the latest timestamp, and if new_state is Config_Testing. Any idea how?

Incident No.	new_state	action_timestamp
PHD200341954	Config_Testing	Dec 1, 2015 3:44:08 PM
PHD200341944	Submitted	Dec 4, 2015 5:17:55 PM
PHD200341952	Config_Testing	Dec 4, 2015 5:17:55 PM

Kushal_Chawda · ‎2015-12-22

for PHD200341954 how Dec 1, 2015 3:44:08 PM is calculated?

juarez_chu · ‎2015-12-22

Hi Kush, If there is Config_Testing for the Incident No.,then you will select the record with Config_Testing and the latest date. For PHD200341954, you will notice that there is an entry for Dec. 4, but new_state is not Config_Testing. For PHD200341944, there is no record for Config_Testing so you will select only the latest date. Sorry if my explanation is confusing

Kushal_Chawda · ‎2015-12-22

But there is two records for Config_Testing then how will you decide which one to take?

juarez_chu · ‎2015-12-22

you will take the one with the latest timestamp

tresesco · ‎2015-12-22

PFA