Select distinct question..

[juarez_chu]

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

[Chanty4u]

=Count( Distinct [Fieldname])

[juarez_chu]

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]

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.

[juarez_chu]

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]

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

[juarez_chu]

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]

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

[juarez_chu]

you will take the one with the latest timestamp

[tresesco]

PFA