Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
Hi Isabel,
What made you choose +61123456789 instead of +61123456788? What defines first?
Please let me know,
Luis
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!
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.
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
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.
Try this Expression
MaxString(Aggr(Only({<Direction={'Inbound','Outbound'},[Participant Purpose]={'customer'}>} [Participant Purpose]),
[Participant Purpose],(Direction,(LOAD_ORDER,ORIGINAL))))
Hi Antonio,
It didn't work. It returned "-" only
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