Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

SUBSTR and INSTR in 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

 

23 Replies
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;

Anonymous
Not applicable
Author

I am loading the data to check the result.

Anonymous
Not applicable
Author

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

sunny_talwar

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

sunny_talwar

It worked for O, but not R? Would you be able to share a sample to check?

Anonymous
Not applicable
Author

With the same formula as above, this is what I see in table.

It works fine for I as there is no data so it displays -. But for R, F and Total it shows nothing, though I can see the column.

sunny_talwar

Lol okay... don't know what to say... I have no idea what you working with ....