Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

loveqliksense
New Contributor III

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

Re: SUBSTR and INSTR in Qliksense

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;

loveqliksense
New Contributor III

Re: SUBSTR and INSTR in Qliksense

I am loading the data to check the result.

loveqliksense
New Contributor III

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

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

loveqliksense
New Contributor III

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

praveen70488
New Contributor

Re: SUBSTR and INSTR in Qliksense

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

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

loveqliksense
New Contributor III

Re: SUBSTR and INSTR in Qliksense

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.

Re: SUBSTR and INSTR in Qliksense

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

Community Browser