Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
check if you see the same thing.
I can confirm that I see the same thing from your screenshot which you have pasted above.
How to get this count in Qlik ?
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
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.
Try
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
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.
Suppose there is OOOOOO, then count should give me 6 .