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

[sunny_talwar]

Really? that is all you wanted? Try this

SubStringCount('OOOOOO', 'O')

[Anonymous]

I sent a mail to BSA as:

I have a question

will the values be in the O6O or  in OOOOOO pattern ?

If its OOOOOO and the value should be 6 or 6O?

Waiting for the reply.

I think,  if OOOOOO, then count = 6 .

lets see .

[Anonymous]

Nope OOOOOO is not the correct pattern client wants.

BSA replied as pattern is O6OR3RF2F' and value to displayed is 6 for open claims count.

[sunny_talwar]

Buddy, your last 8-9 responses have made very little sense to me... Is English not your native language? may be you can seek help from someone who can speak your native language.

Please don't take me wrong my friend. I am just trying to help, but you either have been ignoring my comments or you don't understand what I am asking for. May be you might find it easy to talk with someone in your native language.

All the very best!!