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

[juarez_chu]

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]

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 · Accepted solution]

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]

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]

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]

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]

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]

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]

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]

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