Recovered lines : 0

[MarioCenteno]

I have problem when try load data show me message "Recovered lines : 0" . Sometines if load the data, others will not.

Does anyone know why? Used Qlik Sense Desktop.

[Thiago_Justen_]

Mario,

Esse filtro aqui deve estar resumindo sua consulta a 0.

AND b.fecha_proceso >=  To_date('20180701','YYYYMMDD') and b.fecha_proceso <= To_date('20180723','YYYYMMDD')

Verifique a possibilidade de comentar esta linha apenas para testar se a minha suspeita se confirma.

Abs e Sucesso!!

[MarioCenteno]

Hi

That filter I have to meustre the current month, to be a large amount of records, I have already tested by placing a rownum <10 in the where and there if it shows data.

[balabhaskarqlik]

I just modified the script a bit, what are the values you are seeing for csmo_ant field, when you execute the Query?

[VENTA_GC_2018]:

LOAD

[COD_UNICOM],

[CICLO],

[NIS_RAD],

[NOMBRE_CLIENTE],

[CSMO_ANT],

[CSMO_ACTUAL],

[DESC_TAR];

SELECT

b.cod_unicom,b.ciclo,b.nis_rad,

c.nom_cli || ' ' || c.ape1_cli|| ' ' || c.ape2_cli AS NOMBRE_CLIENTE,

//--SubQuery Csmo_antes--

avg(NVL(

(Select round(sum(hf.csmo_kwh)/3) as csmo

    FROM bi_billing hf

    WHERE hf.nis_rad = b.nis_rad and

    to_char(hf.fecha_proceso,'YYYYMM')  between

    TO_CHAR(add_months(b.fecha_proceso,-4),'YYYYMM') and

    TO_CHAR(add_months(b.fecha_proceso,-2),'YYYYMM') ),0)) as csmo_ant,

sum(b.csmo_kwh) as Csmo_actual,

t.desc_tar

from bi_billing b, bi_tarifas t, BI.MVSUMCLIENTES c

where b.CO_CONCEPTO IN ('CC221','CC250','CC270','CC280','CC320','CC321')

AND B.NIS_RAD = C.NIS_RAD

AND b.TIP_REC IN ('TR010','TR014')

AND b.cod_tar = t.cod_tar

//AND b.cod_unicom = 4111

AND T.DEMANDA IN ('GRANDES','MEDIANAS')

//AND ROWNUM <10

//AND B.nis_Rad in(3105136)

AND b.fecha_proceso >= To_date('20180701','YYYYMMDD') and

        b.fecha_proceso <= To_date('20180723','YYYYMMDD')

group by b.cod_unicom,b.ciclo, b.nis_rad,b.nis_rad,t.desc_tar,c.nom_cli,c.ape1_cli,c.ape2_cli;

[MarioCenteno]

Average of the last 3 months of sales.

[MarioCenteno]

Will there be any way to get the average of the last 3 months of sales without taking into account the current month in Qllik Sense?

[MarioCenteno]

Will there be any way to get the average of the last 3 months of sales without taking into account the current month in Qllik Sense?

[MarioCenteno · Accepted solution]

I already solved it, stop using the subquery and upload data for the last 4 months. Then take the average of the last 3 months in a function vs the current consumption.

The avg last 3 months:

SUM({$<[FECHA_PROCESO.autoCalendar.Date]=

{'>=$(=AddMonths(Today(),-4))<=$(=AddMonths(Today(),-1))'}>}CSMO_ACTUAL/3)

Current month

SUM({<[FECHA_PROCESO.autoCalendar.Year]={'$(vMaxYear)'},

[FECHA_PROCESO.autoCalendar.Month]={'$(vMaxMonth)'},[FECHA_PROCESO.autoCalendar.Day]={'<=$(vMaxDay)'},

FECHA_PROCESO=,[FECHA_PROCESO.autoCalendar.YearMonth]=>}CSMO_ACTUAL)

Thanks for the help

[balabhaskarqlik]

Wow, that's good to hear, finally you solved your issue.