SUBSTR and INSTR in Qliksense

[Anonymous]

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

[Anonymous]

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.

[sunny_talwar]

check if you see the same thing.

I can confirm that I see the same thing from your screenshot which you have pasted above.

[Anonymous]

How to get this count in Qlik ?

[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

[Anonymous]

[Anonymous]

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.

[sunny_talwar]

Try

[Anonymous]

Made no difference. Instead of O6O we need to get 6. I mean to say there will be N number of O and we need the count of that.

let me add +1 here as well

Mid(my_claim_count_string, Index(my_claim_count_string, 'O', 1)+1 and see

[sunny_talwar]

Once again, I want to help you... but all I am asking for you is to give me 2-3 different values and what do you expect to see out of it.... because I am not entirely sure what you are looking to get.

[Anonymous]

Suppose there is OOOOOO, then count should give me 6 .