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
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;
I am loading the data to check the result.
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
Sorry, I am not sure I understand what you are looking for?
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
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
It worked for O, but not R? Would you be able to share a sample to check?
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.
Lol okay... don't know what to say... I have no idea what you working with ....