Solved: Re: Select distinct question.. - Page 2 - 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

juarez_chu · ‎2015-12-22

hi tresesco, thank you but I am unable to open your file as I am using personal edition only..can you please share the script? thank you so much

Chanty4u · ‎2015-12-22

Edit script:

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'

];

Dimension:Incidentno.

Exp1:

If(IsNull(max({<new_state={'Config_Testing'}>}action_timestamp)), Max(action_timestamp),max({<new_state={'Config_Testing'}>}action_timestamp))

ExP2:

If(IsNull(FirstSortedValue({<new_state={'Config_Testing'}>}new_state,-action_timestamp)),

FirstSortedValue(new_state,-action_timestamp),FirstSortedValue({<new_state={'Config_Testing'}>}new_state,-action_timestamp))

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.

gandalfgray · ‎2015-12-22

I would do like this:

stateMap:

Mapping

LOAD * INLINE [

F1, F2

Config_Testing, 1

];

tmp:

LOAD [Incident No.] As key,

new_state,

ApplyMap('stateMap',new_state,2) As state_order,

action_timestamp

FROM your_original_table;

the_wanted_table:

LOAD key As [Incident No.],

new_state,

action_timestamp

Resident tmp

Where Not Exists([Incident No.],key)

Order By state_order,action_timestamp desc;

DROP Table tmp;

the ApplyMap assigns 1 to state_order for all records where new_state is Config_testing, all others get the default 2.

I then resident loads the data from the tmp table sorting by that created state_order and then by acttion_timestamp in descending order.

The Not Exists clause make sure I only get one of each [Incident No.] (I have to rename the field in the first load, so I can use Not Exists in the second load)

/gg

Kushal_Chawda · ‎2015-12-22

another

Data:

LOAD *,timestamp(Timestamp#(action_timestamp,'MMM DD, YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss TT') as Time Inline [

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"];

Left Join (Data)

LOAD [Incident No.],

[Incident No.] as Incident_CT,

new_state,

timestamp(max(Time),'DD/MM/YYYY hh:mm:ss TT') as TimeFinal

Resident Data

where new_state='Config_Testing'

Group by [Incident No.],new_state;

Join (Data)

LOAD [Incident No.],

new_state,

timestamp(max(Time),'DD/MM/YYYY hh:mm:ss TT') as TimeFinal

Resident Data

where not Exists(Incident_CT,[Incident No.])

Group by [Incident No.],new_state;

DROP Field Incident_CT;

dgreenberg · ‎2015-12-22

Why not use this in the load script. It works for me in similar situations and is very simple.

Load

FirstSortedValue([Incident No.],action_timestamp) as [Incident No.]

Group by [Incident No]

...

The only caveat with this is if there are 2 or more identical values for the same [Incident No.],action_timestamp it would return a null. In your case you could avoid that with something like this:

FirstSortedValue([Incident No.], action_timestamp + rand()) as [Incident No.]

Here is my actual use case (note the use of - means largest not smallest value)

EMPLOYEE:
LOAD
FirstSortedValue(pm_pri_ind_segmt_s_s, -effdt_t_s) as pm_pri_ind_segmt_s_s,
FirstSortedValue(pm_sec_ind_segmt_s_s, -effdt_t_s) as pm_sec_ind_segmt_s_s,
firstSortedValue(emplid_s_s,-effdt_t_s) as emplid_s_s,
firstSortedValue(name_s_s, -effdt_t_s ) as name_s_s,
firstSortedValue(effdt_t_s, -effdt_t_s) as effFromDt
Group by name_s_s;

Load *
FROM
[employees.csv]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

maxgro · ‎2015-12-22

Script

I want the first incident (where peek, in bold)

order by new state (config testing first) or, when there isn't any config testing, action timestamp descending

Data:

LOAD

[Incident No.],

new_state,

timestamp(Timestamp#(action_timestamp,'MMM D, YYYY h:mm:ss tt'), 'MMM D, YYYY h:mm:ss TT')

as action_timestamp,

if(new_state='Config_Testing', 1, 2) as sort

/* 1 because config_testing is the first to choose, 2 because for other state the order is by timestamp */

Inline [

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"];

Final:

load [Incident No.], new_state, action_timestamp

Resident Data

Where Peek('Incident No.') <> [Incident No.]

order by [Incident No.], sort, action_timestamp desc;

DROP Table Data;

juarez_chu · ‎2015-12-22

thanks tresesco, got what I needed using this.

another question, I actually have lots of other columns, and I also need them..i am trying to add LOAD *, in your code but it getting me script error, any idea how?

juarez_chu · ‎2015-12-22

thanks maxgro, this one works as well and is very easy to understand

I actually have lots of other columns, and I also need them..i am trying to add LOAD *, in your code but it getting me script error, any idea how?

maxgro · ‎2015-12-23

maybe a duplicated field in the load but I can't see your script and your error