Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have an issue with an SQL used in an Oracle database. When I use it in the Data Loader on Qlik Sense it doesn't produce any values, it just produces NULL values. The SQL works fine on the Oracle database.
I hope some of you fine people here can help me.
LOAD *
;
SQL select FIRST_CHANNEL_GROUP,
HOVED_PRODUKT_GRUPPE,
nvl(BUDGET_PRODUCT, 'No Budget Product') as BUDGET_PRODUCT,
sum(CASE WHEN report_type = 'Gained' THEN antal ELSE 0 END) as GROSS_INTAKE,
sum(CASE WHEN report_type = 'Churn' AND report_type_kube = 'Churn' AND report_type_segm = 'Churn before 31 days' THEN antal ELSE 0 END) as ANNULLMENTS,
sum(CASE WHEN report_type = 'Churn' AND report_type_kube = 'Churn' AND report_type_segm = 'Churn' THEN antal ELSE 0 END) as CHURN,
sum(CASE WHEN report_type = 'Migrering TIL' THEN antal ELSE 0 END)-sum(CASE WHEN report_type = 'Migrering FRA' THEN antal ELSE 0 END) AS MIGRATIONS,
(select nvl(budget, 0) from MPR_BUDGET where BUDGET_KPI = 'Activations (budget)' and YEAR_MONTH = '01-06-2017' and PRODUCT_SEGMENT = MPR_F0063_CUSTOMER.HOVED_PRODUKT_GRUPPE and PRODUCT_SUBGROUP = MPR_F0063_CUSTOMER.BUDGET_PRODUCT and CHANNEL = MPR_F0063_CUSTOMER.FIRST_CHANNEL_GROUP) as BUDGET,
(select nvl(budget, 0) from MPR_BUDGET where BUDGET_KPI = 'Activations (forecast)' and YEAR_MONTH = '01-06-2017' and PRODUCT_SEGMENT = MPR_F0063_CUSTOMER.HOVED_PRODUKT_GRUPPE and PRODUCT_SUBGROUP = MPR_F0063_CUSTOMER.BUDGET_PRODUCT and CHANNEL = MPR_F0063_CUSTOMER.FIRST_CHANNEL_GROUP) as FORECAST_BUDGET,
(select nvl(budget, 0) from MPR_BUDGET where BUDGET_KPI = 'Activations (reported)' and YEAR_MONTH = '01-06-2017' and PRODUCT_SEGMENT = MPR_F0063_CUSTOMER.HOVED_PRODUKT_GRUPPE and PRODUCT_SUBGROUP = MPR_F0063_CUSTOMER.BUDGET_PRODUCT and CHANNEL = MPR_F0063_CUSTOMER.FIRST_CHANNEL_GROUP) as REPORTED_BUDGET
from MPR_F0063_CUSTOMER
where YYYYMM = '201706'
group by FIRST_CHANNEL_GROUP, HOVED_PRODUKT_GRUPPE, BUDGET_PRODUCT
order by FIRST_CHANNEL_GROUP, HOVED_PRODUKT_GRUPPE, BUDGET_PRODUCT
It is BUDGET, FORECAST_BUDGET and REPORTED_BUDGET that only comes out as NULL
My guess is Qlik is not properly handling nvl, could try using COALESCE or CASE to test/replace the nulls
Are you seeing 0 rows come through or seeing multiple rows of data, but all with null values? Also, are you using any variables for the where clause?
Hi Sunny,
I get 216 rows as expected, but the 3 columns are NULL in Qlik Sense.
Which 3?
My guess is Qlik is not properly handling nvl, could try using COALESCE or CASE to test/replace the nulls
Hi Sunny,
These:
It is BUDGET, FORECAST_BUDGET and REPORTED_BUDGET that only comes out as NULL
Hi David,
Thank you for the suggestion, I will try that.