topic Listing specific values with duplicates. in QlikView

Listing specific values with duplicates.

Anonymous — Tue, 21 Apr 2015 12:44:59 GMT

Hi Guys,

Hope this makes sense as I am new to Qlikview but I need help with a Straight Table. (or whatever you think would be best for this)

Based on the below data table I need the table/chart to bring me back values for Entry_ID, EVENT_TYPE AND PROC_ID where PROC_ID has two matching EVENT_TYPE with different ENTRY_ID.

ENTRY_ID is the unique field.

ENTRY ID	EVENT_TYPE	PROC_ID
1	Rain	1001
2	Rain	1001
3	Sunny	1001
4	Cloudy	1003
5	Snow	1004
6	Snow	1004
7	Rain	1005
8	Cloudy	1006

For instance it should return only the fields highlighted in yellow.

Thanks.

Re: Listing specific values with duplicates.

dgreenberg — Tue, 21 Apr 2015 13:13:10 GMT

If you include the Entry ID in the chart it will not be a duplicate it will be a unique row.

What you could do is have EVENT_TYPE and PROC_ID as Dimensions and use this as the expression:

=if(Count([ENTRY ID])>1,'Duplicate')

Then you end up with this:

Rain	1001	Duplicate
Snow	1004	Duplicate

Re: Listing specific values with duplicates.

sunny_talwar — Tue, 21 Apr 2015 13:14:38 GMT

See if this is what you wanted? (PFA)

Table:

LOAD [ENTRY ID],

EVENT_TYPE,

PROC_ID,

EVENT_TYPE&PROC_ID as Join

FROM

[https://community.qlik.com/thread/161016]

(html, codepage is 1252, embedded labels, table is @1);

Join(Table)

LOAD Count(Join) as Count,

Join

Resident Table

Group By Join;

DROP Field Join;

Best,

Sunny

Re: Listing specific values with duplicates.

Tue, 21 Apr 2015 14:12:29 GMT

I got a solution which seems a little bit complicated. Hopefully it helps.

weather:

LOAD * Inline [

entry_id, event_type, proc_id

1,rain,1001

2,rain,1001

3,sunny,1001

4,cloudy,1003

5,snow,1004

6,snow,1004

7,rain,1005

8,cloudy,1006

];

weather_tmp:

LOAD

entry_id,

event_type,

proc_id,

event_type &'#' & proc_id as key

Resident

weather;

key_t:

LOAD

key

where c >1;

LOAD

event_type &'#' & proc_id as key,

count(event_type &'#' & proc_id) as c

resident

weather

Group by

event_type,

proc_id;

DROP Table weather;

weather_new:

left keep(key_t)

LOAD

entry_id,

event_type,

proc_id,

key

Resident

weather_tmp;

DROP Tables weather_tmp, key_t;

DROP Field key;

Re: Listing specific values with duplicates.

Anonymous — Tue, 21 Apr 2015 14:25:46 GMT

Hi sunindia,

This is exactly what I am looking for. However the ENTRY_ID, EVENT_TYPE and PROC_ID fields all come from different tables loaded into my Qlik document. How do I go about joining EVENT_TYPE & PROC_ID then so I still get the same result as your attached?

Regards,

Tim

Re: Listing specific values with duplicates.

anbu1984 — Tue, 21 Apr 2015 14:56:26 GMT

If you have common fields between these tables, then you can try this in Front end

Dim: Entry_ID, EVENT_TYPE AND PROC_ID

Expr: If(Aggr(Count(Entry_Id),EVENT_TYPE ,PROC_ID)>1,1)

Re: Listing specific values with duplicates.

sunny_talwar — Tue, 21 Apr 2015 15:42:59 GMT

There is couple of ways to do it. You can either use mapping load and apply map to bring everything into one table or you can use Join to create a table which join the required field and then you can do all the calculations.

For join: Understanding Join, Keep and Concatenate

For Mapping: Mapping Load in Qlikview: A Qlikview Tutorial « Learn Qlikview‌

HTH

Best,

Sunny