Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

eustisab
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.

Appreciate your help.

   

Sample Data
ID: conversationCLIDirectionParticipant Purpose
1+61123456710Inboundacd
1+61123456711Inboundivr
1+61123456789Inboundcustomer
1+61123456788Inboundcustomer
3+61123456790Inboundacd
2+61123456795Outboundivr
2+61123456799Outboundcustomer
4+61123456889OutboundIvr

  

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

Result
ID: conversationCLIDirectionParticipant Purpose
1+61123456789Inboundcustomer
2+61123456799Outboundcustomer
1 Solution

Accepted Solutions
luismadriz
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
luismadriz
Valued Contributor

Re: return the value that matched the category.

Hi Isabel,

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

Please let me know,

Luis

eustisab
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: conversationDateTimeCLIDirectionParticipant Purpose
111/01/201715:30+61123456710Inboundacd
111/01/201715:30+61123456711Inboundivr
111/01/201715:30+61123456789Inboundcustomer
111/01/201715:30+61123456788Inboundcustomer
311/01/201715:30+61123456790Inboundacd
211/01/201715:30+61123456795Outboundivr
211/01/201715:30+61123456799Outboundcustomer
411/01/201715:30+61123456889OutboundIvr

thank you!

MVP
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:

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.

eustisab
New Contributor III

Re: return the value that matched the category.

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

MVP
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.

antoniotiman
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]),

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

antoniotiman
Honored Contributor III

Re: return the value that matched the category.

eustisab
New Contributor III

Re: return the value that matched the category.

Hi Antonio,

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

luismadriz
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