# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor III

## return the value that matched the category.

Hi Everyone,

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

 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
1 Solution

Accepted Solutions
Valued Contributor

## Re: return the value that matched the category.

Hi Isabel,

Create a table with these dimensions: ID, Date, Direction, Participant Purpose

And this measure:

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

You may need to test with more data,

I hope this helps,

Luis

13 Replies
Valued Contributor

## Re: return the value that matched the category.

Hi Isabel,

Luis

New Contributor III

## Re: return the value that matched the category.

Hi Luis,

From the raw data, I found out that date and time of that id conversation is the same. so i can't rely on that column.

thus, counting the "first customer" will be based on the first data that will appear on the row.

Is this possible?

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

thank you!

MVP

## Re: return the value that matched the category.

To select the "first value" in the front end, I suggest that you add a row count when loading.Or add a flag or FirstCLI to the load script to identify the first CLI value meeting the condtions. Something like:

CLIS:

RowNo() as row,

ID,

CLI,

Direction,

[Participant Purpose],

....

FROM ...

Join(CLIS)

ID,

FirstSortedValue(CLI, row) as CLI,

Direction,

1 as FirstCLIFlag

Resident CLIS

Where [Participant Purpose] = 'customer'

Group By ID, Direction;

Now use the FirstCLIFlag to select the correct CLI when the [Participant Purpose] = customer.

New Contributor III

## Re: return the value that matched the category.

Hi Jonathan,

Sorry I did not mention that the data has already been loaded.

Now what I can do is just create an expression to return only the data with the below condition.

(I do not have acces on the backend. so my concerns will be dealing on visulasations / expressions in front - end)

 Condition: Return CLI for 1st Participant "customer" Purpose INBOUND Direction Return CLI for "customer" Participant Purpose OUTBOUND Direction

Hope this is possible

MVP

## Re: return the value that matched the category.

Well there will be no reliable way to identify the first CLI, unless you disallow interactive sorting and carefully select the sort orders.

The only sort criterion that you could use is "loaded order", and if the load order is the wrong way round, you will not be able to do this in the front end only as you simply don't have the information needed.

Honored Contributor III

## Re: return the value that matched the category.

Try this Expression

MaxString(Aggr(Only({<Direction={'Inbound','Outbound'},[Participant Purpose]={'customer'}>} [Participant Purpose]),

Honored Contributor III

## Re: return the value that matched the category.

New Contributor III

## Re: return the value that matched the category.

Hi Antonio,

It didn't work. It returned "-" only

Valued Contributor

## Re: return the value that matched the category.

Hi Isabel,

Create a table with these dimensions: ID, Date, Direction, Participant Purpose

And this measure:

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

You may need to test with more data,

I hope this helps,

Luis