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

[luismadriz]

Hi Isabel,

What made you choose +61123456789 instead of +61123456788? What defines first?

Please let me know,

Luis

[Anonymous]

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!

[jonathandienst]

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:

LOAD

    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.

[Anonymous]

Hi Jonathan,

Appreciate your help.

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

[jonathandienst]

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.

[antoniotiman]

Try this Expression

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

[Participant Purpose],(Direction,(LOAD_ORDER,ORIGINAL))))

[antoniotiman]

[Anonymous]

Hi Antonio,

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

[luismadriz · Accepted solution]

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