Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
=Count( Distinct [Fieldname])
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?
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.
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 |
for PHD200341954 how Dec 1, 2015 3:44:08 PM is calculated?
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
But there is two records for Config_Testing then how will you decide which one to take?
you will take the one with the latest timestamp
PFA