23 Replies Latest reply: Jul 26, 2018 1:23 PM by Sunny Talwar RSS

    SUBSTR and INSTR in Qliksense

    Love Qliksense

      Hi all,

       

      I have field  "my_claim_count_string",which is in the format of 'O6OR3RF2F' where O is MY_CLAIM_COUNT_O which is 6.

       

      so I added 'O6OR3RF2F' as "my_claim_count_string" in SQL select and in load I need to convert this oracle syntax to work in Qlik.

       

      Oracle syntax:

       

        (

        case

        when INSTR(my_claim_count_string, 'O', 1, 1) > 0 and INSTR(my_claim_count_string, 'O', 1, 2) > 0

          then TO_NUMBER(SUBSTR(my_claim_count_string, INSTR(my_claim_count_string, 'O', 1, 1) + 1, INSTR(my_claim_count_string, 'O', 1, 2) - INSTR(my_claim_count_string, 'O', 1, 1) - 1))

        else

          0

        end

        ) as MY_CLAIM_COUNT_O,

       

      Qliksense syntax??

       

      How can I do this to get O6O ? I changed INSTR to subfield and INSTR to MID

       

       

       

      Thanks

       

       

        • Re: SUBSTR and INSTR in Qliksense
          Sunny Talwar

          May be this

           

          If(Index(my_claim_count_string, 'O', 1) > 0 and Index(my_claim_count_string, 'O', 1) > 0, Mid(my_claim_count_string, Index(my_claim_count_string, 'O', 1), Index(my_claim_count_string, 'O', 2) - Index(my_claim_count_string, 'O', 1) + 1), 0) as String;

          • Re: SUBSTR and INSTR in Qliksense
            Love Qliksense

            Hi Sunny,

             

            It works fine.

             

            Now if we do not know how much is the count in this format 'O6O' and be like OXO, what should be the expression like?

             

            Thanks

              • Re: SUBSTR and INSTR in Qliksense
                Sunny Talwar

                Sorry, I am not sure I understand what you are looking for?

                  • Re: SUBSTR and INSTR in Qliksense
                    Love Qliksense

                    Hi Sunny,

                    I had to convert this for whole O6OR3RF2F', O worked but R , F , I and Total does not work in Qlik but works in oracle

                     

                    Oracle:

                    (

                      case

                      when INSTR(my_claim_count_string, 'O', 1, 1) > 0 and INSTR(my_claim_count_string, 'O', 1, 2) > 0

                        then TO_NUMBER(SUBSTR(my_claim_count_string, INSTR(my_claim_count_string, 'O', 1, 1) + 1, INSTR(my_claim_count_string, 'O', 1, 2) - INSTR(my_claim_count_string, 'O', 1, 1) - 1))

                      else

                        0

                      end

                      ) as MY_CLAIM_COUNT_O,

                      (

                      case

                      when INSTR(my_claim_count_string, 'R', 1, 1) > 0 and INSTR(my_claim_count_string, 'R', 1, 2) > 0

                        then TO_NUMBER(SUBSTR(my_claim_count_string, INSTR(my_claim_count_string, 'R', 1, 1) + 1, INSTR(my_claim_count_string, 'R', 1, 2) - INSTR(my_claim_count_string, 'R', 1, 1) - 1))

                      else

                        0

                      end

                      ) as MY_CLAIM_COUNT_R,

                      (

                      case

                      when INSTR(my_claim_count_string, 'F', 1, 1) > 0 and INSTR(my_claim_count_string, 'F', 1, 2) > 0

                        then TO_NUMBER(SUBSTR(my_claim_count_string, INSTR(my_claim_count_string, 'F', 1, 1) + 1, INSTR(my_claim_count_string, 'F', 1, 2) - INSTR(my_claim_count_string, 'F', 1, 1) - 1))

                      else

                        0

                      end

                      ) as MY_CLAIM_COUNT_F,

                      (

                      case

                      when INSTR(my_claim_count_string, 'I', 1, 1) > 0 and INSTR(my_claim_count_string, 'I', 1, 2) > 0

                        then TO_NUMBER(SUBSTR(my_claim_count_string, INSTR(my_claim_count_string, 'I', 1, 1) + 1, INSTR(my_claim_count_string, 'I', 1, 2) - INSTR(my_claim_count_string, 'I', 1, 1) - 1))

                      else

                        0

                      end

                      ) as MY_CLAIM_COUNT_I

                     

                     

                    Qlik :

                      If(Index(my_claim_count_string, 'O', 1) > 0 and Index(my_claim_count_string, 'O', 1) > 0, Mid(my_claim_count_string, Index(my_claim_count_string, 'O', 1),

                         Index(my_claim_count_string, 'O', 2) - Index(my_claim_count_string, 'O', 1) + 1), 0) as "MY_CLAIM_COUNT_O",

                        

                          If(Index(my_claim_count_string, 'R', 1) > 0 and Index(my_claim_count_string, 'R', 1) > 0, Mid(my_claim_count_string, Index(my_claim_count_string, 'R', 1),

                        Index(my_claim_count_string, 'R', 2) - Index(my_claim_count_string, 'R', 1) + 1), 0) as "MY_CLAIM_COUNT_R",

                     

                      If(Index(my_claim_count_string, 'F', 1) > 0 and Index(my_claim_count_string, 'F', 1) > 0, Mid(my_claim_count_string, Index(my_claim_count_string, 'F', 1),

                          Index(my_claim_count_string, 'F', 2) - Index(my_claim_count_string, 'F', 1) + 1), 0) as "MY_CLAIM_COUNT_F",

                        

                         If(Index(my_claim_count_string, 'I', 1) > 0 and Index(my_claim_count_string, 'I', 1) > 0, Mid(my_claim_count_string, Index(my_claim_count_string, 'I', 1),

                         Index(my_claim_count_string, 'I', 2) - Index(my_claim_count_string, 'I', 1) + 1), 0) as "MY_CLAIM_COUNT_I",

                        

                          ( If(Index(my_claim_count_string, 'O', 1) > 0 and Index(my_claim_count_string, 'O', 1) > 0, Mid(my_claim_count_string, Index(my_claim_count_string, 'O', 1),

                        Index(my_claim_count_string, 'O', 2) - Index(my_claim_count_string, 'O', 1) + 1), 0)  +

                         If(Index(my_claim_count_string, 'R', 1) > 0 and Index(my_claim_count_string, 'R', 1) > 0, Mid(my_claim_count_string, Index(my_claim_count_string, 'R', 1),

                         Index(my_claim_count_string, 'R', 2) - Index(my_claim_count_string, 'R', 1) + 1), 0) +

                           If(Index(my_claim_count_string, 'F', 1) > 0 and Index(my_claim_count_string, 'F', 1) > 0, Mid(my_claim_count_string, Index(my_claim_count_string, 'F', 1),

                           Index(my_claim_count_string, 'F', 2) - Index(my_claim_count_string, 'F', 1) + 1), 0) +

                        

                        If(Index(my_claim_count_string, 'I', 1) > 0 and Index(my_claim_count_string, 'I', 1) > 0, Mid(my_claim_count_string, Index(my_claim_count_string, 'I', 1),

                         Index(my_claim_count_string, 'I', 2) - Index(my_claim_count_string, 'I', 1) + 1), 0)) as "MY_CLAIM_COUNT_TOTAL"

                     

                    Could you please help?

                    Values in db from query is O is 6, R is 3, F is 2 , I is 0 and total is 11.

                     

                    Thanks

                • Re: SUBSTR and INSTR in Qliksense
                  praveen kumar

                  You can try this

                   

                    If(Index(my_claim_count_string, 'O', 1) > 0 and Index(my_claim_count_string, 'O', 2) > 0, Mid(my_claim_count_string, Index(my_claim_count_string, 'O', 1)+1,

                       Index(my_claim_count_string, 'O', 2) - Index(my_claim_count_string, 'O', 1) + 1), 0) as "MY_CLAIM_COUNT_O",

                  For your all field and for total you can calculate total in frontend expression like sum(MY_CLAIM_COUNT_O)+sum(MY_CLAIM_COUNT_R)+etc. to calculàte  total fields

                  • Re: SUBSTR and INSTR in Qliksense
                    Love Qliksense

                    This is what I see now. could you please add I,F ,R ant total in the app where you added O and check if you see the same thing.

                     

                    • Re: SUBSTR and INSTR in Qliksense
                      Love Qliksense

                      How to get this count in Qlik ?

                       

                        • Re: SUBSTR and INSTR in Qliksense
                          Sunny Talwar

                          I think we are going to continue playing the game where you are going to pretend that you have not read what I am saying and I am going to pretend that I have not read what you are saying. Having said that, the answer to your above questions is the following

                          How to get this count in Qlik ?

                          We are going to take a sky dive to get this done

                        • Re: SUBSTR and INSTR in Qliksense
                          Love Qliksense

                          If(Index(my_claim_count_string, 'O', 1) > 0 and Index(my_claim_count_string, 'O', 1)

                           

                          Index(my_claim_count_string, 'O', 1)+1, should not the number start from here , because both the if is identical here.