13 Replies Latest reply: Nov 26, 2017 10:59 PM by Luis Madriz RSS

    return the value that matched the category.

    Isabel Encinas

      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
        • Re: return the value that matched the category.
          Luis Madriz

          Hi Isabel,

           

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

           

          Please let me know,

           

          Luis

            • Re: return the value that matched the category.
              Isabel Encinas

              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!

                • Re: return the value that matched the category.
                  Jonathan Dienst

                  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.

                  • Re: return the value that matched the category.
                    Luis Madriz

                    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

                      • Re: return the value that matched the category.
                        Isabel Encinas

                        Hi Luis,

                         

                        thanks.

                            

                        Sample Data
                        ID: conversationCLIDateDirectionParticipant Purpose
                        16112345671011/01/2017Inboundacd
                        16112345671111/01/2017Inboundivr
                        16112345678911/01/2017Inboundcustomer
                        16112345678811/01/2017Inboundcustomer
                        36112345679011/01/2017Inboundacd
                        26112345679511/01/2017Outboundivr
                        26112345679911/01/2017Outboundcustomer
                        46112345688911/01/2017OutboundIvr

                         

                         

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

                        Result of the given expression
                        ID: conversationCLIDateDirectionParticipant Purpose
                        1 -11/01/2017Inboundacd
                        1 -11/01/2017Inboundivr
                        16112345678911/01/2017Inboundcustomer
                        16112345678811/01/2017Inboundcustomer
                        3 -11/01/2017Inboundacd
                        2 -11/01/2017Outboundivr
                        26112345679911/01/2017Outboundcustomer
                        4 -11/01/2017OutboundIvr

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

                         

                            

                        Desired Result
                        ID: conversationCLIDateDirectionParticipant Purpose
                        16112345678911/01/2017Inboundcustomer
                        26112345679911/01/2017Outboundcustomer

                         

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