topic Re: Convert SQL code Qlik code in App Development

Convert SQL code Qlik code

Naps_230 — Tue, 05 Jul 2022 14:28:05 GMT

Hi,

I have some huge requirement script level, we need to change SQL code to Qlik code in script level.

attached the sql code.

Re: Convert SQL code Qlik code

Vegar — Tue, 05 Jul 2022 15:00:15 GMT

You posted a large script sample for us to look at What part in this script lies you issue? Could you try to strip your SQL.txt down so you can point out where your issues lies?

Re: Convert SQL code Qlik code

Naps_230 — Tue, 05 Jul 2022 15:33:50 GMT

Hi ,

can you please give suggestion on below part. basically that case statement part

XCOPRI_Nbrx:
select * from (
select * from (
SELECT
XREF.ims_prescriber_id AS IMS_ID,
XREF.hcp_id AS SKLSI_ID,
--XREF.hcp_name AS HCP_NAME,
MCA.address_line_1_text AS ADDRESS_LINE_1,
MCA.adress_line_2_text AS ADDRESS_LINE_2,
MCA.city_name AS CITY,
MCA.state_name AS STATE,
MCA.postal_code_number AS ZIP,
MCA.territory_id AS TERRITORY_ID,
MCA.territory_name AS TERRITORY_NAME,
MCA.region_id AS REGION_ID,
MCA.region_name AS REGION_NAME,
MCA.area_id AS AREA_ID,
MCA.area_name AS AREA_NAME,
MKT.cstm_prod_grp_value,
F.prod_group_id,
MKT.PRODUCT_GROUP,
XREF.PDRP_FLAG,
P.trimester_of_year_cal,
CASE
When XREF.PDRP_FLAG='Y' THEN 'PDRP' Else XREF.hcp_name End as HCP_NAME1,
CASE
WHEN MKT.mft_def_desc = 'Total Epilepsy Market'
AND P.rnk BETWEEN '1' AND '128' THEN Sum(total_rx)else 0
END AS TOTAL_EPI_MKT_TRX,
CASE
WHEN MKT.mft_def_desc = 'Target Brand'
AND cstm_prod_grp_value = 'APTIOM' and P.RNK BETWEEN '1' AND '128' THEN
Sum(total_rx) else 0
END AS APTIOM_TRX,
CASE
WHEN MKT.mft_def_desc = 'Target Brand'
AND cstm_prod_grp_value = 'BRIVIACT' and P.RNK BETWEEN '1' AND '128' THEN
Sum(total_rx) else 0
END AS BRIVIACT_TRX,
CASE
WHEN MKT.mft_def_desc = 'Target Brand'
AND cstm_prod_grp_value = 'FYCOMPA' and P.RNK BETWEEN '1' AND '128' THEN
Sum(total_rx) else 0
END AS FYCOMPA_TRX,
CASE
WHEN MKT.mft_def_desc = 'Target Brand'
AND cstm_prod_grp_value = 'VIMPAT' and P.RNK BETWEEN '1' AND '128' THEN
Sum(total_rx) else 0
END AS VIMPAT_TRX,
CASE
WHEN
cstm_prod_grp_value = 'XCOPRI'
AND P.rnk BETWEEN '1' AND '128' THEN Sum(total_rx) else 0
END AS XCOPRI_TRX,
CAST(NULL AS INT) AS TOTAL_EPI_MKT_NBRX,
CAST(NULL AS INT) AS APTIOM_NBRX,
CAST(NULL AS INT) AS BRIVIACT_NBRX,
CAST(NULL AS INT) AS FYCOMPA_NBRX,
CAST(NULL AS INT) AS VIMPAT_NBRX ,
P.week_ending_date AS DATA_WEEK_DATE,
case when XREF.priority_text = 'Target' or IncludeIMS.Include_IMS_PRESCRIBER_ID is not null then '1' else '0' end flag,
F.CHANNEL_INDICATOR //uncomment
FROM $(v_Revised_Dremio_Path)VDS_V_FCT_TRX_WEEKLY_ALL F;

Re: Convert SQL code Qlik code

Naps_230 — Tue, 05 Jul 2022 17:12:56 GMT

Hi ,

Please give me suggestion how to write Qlikvew code in stead of below Sql code.

CASE
When XREF.PDRP_FLAG='Y' THEN 'PDRP' Else XREF.hcp_name End as HCP_NAME1,
CASE
WHEN MKT.mft_def_desc = 'Total Epilepsy Market'
AND P.rnk BETWEEN '1' AND '128' THEN Sum(total_rx)else 0
END AS TOTAL_EPI_MKT_TRX,
CASE
WHEN MKT.mft_def_desc = 'Target Brand'
AND cstm_prod_grp_value = 'APTIOM' and P.RNK BETWEEN '1' AND '128' THEN
Sum(total_rx) else 0
END AS APTIOM_TRX,
CASE
WHEN MKT.mft_def_desc = 'Target Brand'
AND cstm_prod_grp_value = 'BRIVIACT' and P.RNK BETWEEN '1' AND '128' THEN
Sum(total_rx) else 0
END AS BRIVIACT_TRX,
CASE
WHEN MKT.mft_def_desc = 'Target Brand'
AND cstm_prod_grp_value = 'FYCOMPA' and P.RNK BETWEEN '1' AND '128' THEN
Sum(total_rx) else 0
END AS FYCOMPA_TRX,
CASE
WHEN MKT.mft_def_desc = 'Target Brand'
AND cstm_prod_grp_value = 'VIMPAT' and P.RNK BETWEEN '1' AND '128' THEN
Sum(total_rx) else 0
END AS VIMPAT_TRX,
CASE
WHEN
cstm_prod_grp_value = 'XCOPRI'
AND P.rnk BETWEEN '1' AND '128' THEN Sum(total_rx) else 0
END AS XCOPRI_TRX,
CAST(NULL AS INT) AS TOTAL_EPI_MKT_NBRX,
CAST(NULL AS INT) AS APTIOM_NBRX,
CAST(NULL AS INT) AS BRIVIACT_NBRX,
CAST(NULL AS INT) AS FYCOMPA_NBRX,
CAST(NULL AS INT) AS VIMPAT_NBRX ,
P.week_ending_date AS DATA_WEEK_DATE,
case when XREF.priority_text = 'Target' or IncludeIMS.Include_IMS_PRESCRIBER_ID is not null then '1' else '0' end flag,

Re: Convert SQL code Qlik code

MarcoWedel — Tue, 05 Jul 2022 22:16:54 GMT

instead of "case" you could use the If() function:

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ConditionalFunctions/if.htm

Re: Convert SQL code Qlik code

Naps_230 — Wed, 06 Jul 2022 10:32:37 GMT

Hi ,

Could you please help on below part, i have try from my side create Qlik code, getting issue.

SQL CODE:

CASE
WHEN MKT.mft_def_desc = 'Target Brand'
AND cstm_prod_grp_value = 'APTIOM' and P.RNK BETWEEN '1' AND '128' THEN
Sum(total_rx) else 0
END AS APTIOM_TRX,

Qlik code:

if(MKT.mft_def_desc = 'Target Brand',
if(cstm_prod_grp_value = 'APTIOM',
if(P.RNK>= 1 and P.RNK <= 128,Sum(total_rx),0))) as APTIOM_TRX,

Re: Convert SQL code Qlik code

MarcoWedel — Sat, 09 Jul 2022 13:19:13 GMT

what issue do you get?