return the value that matched the category.

[Anonymous]

Hi Everyone,

Really new in Qlik. and I have no idea how to start an expression with the below Condition.

Appreciate your help.

Sample Data
ID: conversation	CLI	Direction	Participant Purpose
1	+61123456710	Inbound	acd
1	+61123456711	Inbound	ivr
1	+61123456789	Inbound	customer
1	+61123456788	Inbound	customer
3	+61123456790	Inbound	acd
2	+61123456795	Outbound	ivr
2	+61123456799	Outbound	customer
4	+61123456889	Outbound	Ivr

Condition:

Return CLI for 1st Participant "customer" Purpose INBOUND Direction

Return CLI for "customer" Participant Purpose OUTBOUND Direction

Result
ID: conversation	CLI	Direction	Participant Purpose
1	+61123456789	Inbound	customer
2	+61123456799	Outbound	customer

[Anonymous]

Hi Luis,

thanks.

Sample Data
ID: conversation	CLI	Date	Direction	Participant Purpose
1	61123456710	11/01/2017	Inbound	acd
1	61123456711	11/01/2017	Inbound	ivr
1	61123456789	11/01/2017	Inbound	customer
1	61123456788	11/01/2017	Inbound	customer
3	61123456790	11/01/2017	Inbound	acd
2	61123456795	11/01/2017	Outbound	ivr
2	61123456799	11/01/2017	Outbound	customer
4	61123456889	11/01/2017	Outbound	Ivr

     =FirstSortedValue( DISTINCT {<[Participant Purpose]={customer},Direction={Inbound,Outbound}>} CLI, Date)

Result of the given expression
ID: conversation	CLI	Date	Direction	Participant Purpose
1	-	11/01/2017	Inbound	acd
1	-	11/01/2017	Inbound	ivr
1	61123456789	11/01/2017	Inbound	customer
1	61123456788	11/01/2017	Inbound	customer
3	-	11/01/2017	Inbound	acd
2	-	11/01/2017	Outbound	ivr
2	61123456799	11/01/2017	Outbound	customer
4	-	11/01/2017	Outbound	Ivr

It still shows the 2 Inbound. And "-" for the CLI that doesn't matched the critea.

Desired Result
ID: conversation	CLI	Date	Direction	Participant Purpose
1	61123456789	11/01/2017	Inbound	customer
2	61123456799	11/01/2017	Outbound	customer

Is there a way to only show the data that matched all the criteria?

[luismadriz]

HI I,

Did you create the table with only the 4 dimension and 1 measure as I described. It's strange because I'm getting the results here. Please review this screenshot:

[Anonymous]

Hi Luis,

oooohhh I see. Now it is working.

But I need to add more columns to it.. when I'm adding, the "-" is showing.

columns I should show are:

1. ID

2. CLI

3. DATE

4. START TIME

5. DIRECTION

6. MEDIA TYPE

7. USER NAME

8. QUEUE NAME

9. AHT

10. ATT

11.HOLD

12. ACW

13. ENTRY POINT

14. REASON

15. SUB-REASON

16. PROCESS

17. WRAP UP NAME

PARTICIPANT PURPOSE IS NOT INCLUDED ON THE COLUMNS TO SHOW. but we will based one of the criteria there. PARTICIPANT PURPOSE = customer

I just can't re arrange the format of the columns as they are standard.

[luismadriz]

Hi,

Maybe you need to create a new post attaching the data or the qvf?

It's difficult to troubleshoot without more information,

I hope this helps,

Cheers,

Luis