topic Re: Select distinct question.. in QlikView

Select distinct question..

juarez_chu — Tue, 22 Dec 2015 04:25:23 GMT

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

Re: Select distinct question..

Chanty4u — Tue, 22 Dec 2015 05:06:44 GMT

=Count( Distinct [Fieldname])

Re: Select distinct question..

juarez_chu — Tue, 22 Dec 2015 05:30:34 GMT

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?

Re: Select distinct question..

HirisH_V7 — Tue, 22 Dec 2015 06:07:20 GMT

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.

Re: Select distinct question..

juarez_chu — Tue, 22 Dec 2015 06:15:42 GMT

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

Re: Select distinct question..

Kushal_Chawda — Tue, 22 Dec 2015 06:57:38 GMT

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

Re: Select distinct question..

juarez_chu — Tue, 22 Dec 2015 07:05:22 GMT

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

Re: Select distinct question..

Kushal_Chawda — Tue, 22 Dec 2015 07:08:39 GMT

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

Re: Select distinct question..

juarez_chu — Tue, 22 Dec 2015 07:10:38 GMT

you will take the one with the latest timestamp

Re: Select distinct question..

tresB — Tue, 22 Dec 2015 07:29:29 GMT

PFA

Re: Select distinct question..

juarez_chu — Tue, 22 Dec 2015 07:37:04 GMT

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

Re: Select distinct question..

Chanty4u — Tue, 22 Dec 2015 08:28:40 GMT

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))

Re: Select distinct question..

tresB — Tue, 22 Dec 2015 08:55:32 GMT

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.

Re: Select distinct question..

gandalfgray — Tue, 22 Dec 2015 09:04:53 GMT

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

Re: Select distinct question..

Kushal_Chawda — Tue, 22 Dec 2015 12:09:21 GMT

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;

Re: Select distinct question..

dgreenberg — Tue, 22 Dec 2015 17:35:36 GMT

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);

Re: Select distinct question..

maxgro — Tue, 22 Dec 2015 21:43:48 GMT

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;

Re: Select distinct question..

juarez_chu — Wed, 23 Dec 2015 01:58:10 GMT

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?

Re: Select distinct question..

juarez_chu — Wed, 23 Dec 2015 02:06:49 GMT

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?

Re: Select distinct question..

maxgro — Wed, 23 Dec 2015 18:46:31 GMT

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