topic Re: SUBSTR and INSTR in Qliksense in Visualization and Usability

SUBSTR and INSTR in Qliksense

Anonymous — Wed, 25 Jul 2018 19:05:41 GMT

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

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 — Wed, 25 Jul 2018 19:40:53 GMT

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

Anonymous — Wed, 25 Jul 2018 20:19:08 GMT

I am loading the data to check the result.

Re: SUBSTR and INSTR in Qliksense

Anonymous — Wed, 25 Jul 2018 20:55:42 GMT

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 — Wed, 25 Jul 2018 22:20:44 GMT

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

Re: SUBSTR and INSTR in Qliksense

Anonymous — Thu, 26 Jul 2018 03:05:31 GMT

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

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

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

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

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

Anonymous — Thu, 26 Jul 2018 06:04:05 GMT

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

sunny_talwar — Thu, 26 Jul 2018 11:09:30 GMT

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

Re: SUBSTR and INSTR in Qliksense

Anonymous — Thu, 26 Jul 2018 13:16:30 GMT

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

sunny_talwar — Thu, 26 Jul 2018 13:30:24 GMT

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

Re: SUBSTR and INSTR in Qliksense

Anonymous — Thu, 26 Jul 2018 14:15:19 GMT

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

sunny_talwar — Thu, 26 Jul 2018 14:21:13 GMT

check if you see the same thing.

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

Re: SUBSTR and INSTR in Qliksense

Anonymous — Thu, 26 Jul 2018 14:35:32 GMT

How to get this count in Qlik ?

Re: SUBSTR and INSTR in Qliksense

sunny_talwar — Thu, 26 Jul 2018 14:40:45 GMT

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

Anonymous — Thu, 26 Jul 2018 14:43:16 GMT

Re: SUBSTR and INSTR in Qliksense

Anonymous — Thu, 26 Jul 2018 16:06:05 GMT

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.

Re: SUBSTR and INSTR in Qliksense

sunny_talwar — Thu, 26 Jul 2018 16:07:24 GMT

Try

Re: SUBSTR and INSTR in Qliksense

Anonymous — Thu, 26 Jul 2018 16:23:07 GMT

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

Re: SUBSTR and INSTR in Qliksense

sunny_talwar — Thu, 26 Jul 2018 16:25:10 GMT

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.

Re: SUBSTR and INSTR in Qliksense

Anonymous — Thu, 26 Jul 2018 16:36:06 GMT

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