Listing specific values with duplicates.

[Anonymous]

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.

[dgreenberg]

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

[sunny_talwar · Accepted solution]

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

[#4]

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;

[Anonymous]

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

[anbu1984]

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)

[sunny_talwar]

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