topic Re: Recovered lines : 0 in Brasil

Recovered lines : 0

MarioCenteno — Mon, 23 Jul 2018 15:31:52 GMT

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.

Re: Recovered lines : 0

YoussefBelloum — Mon, 23 Jul 2018 15:51:24 GMT

Hi,

I recommend you add more details here, like the script you're using, screen shot from the error message..etc

Re: Recovered lines : 0

MarioCenteno — Mon, 23 Jul 2018 16:10:51 GMT

It shows me the following message

Help me please.

Re: Recovered lines : 0

Thiago_Justen_ — Mon, 23 Jul 2018 16:55:06 GMT

Mario,

It seems like your query doesn't match anything. It will be good if you could take a scrrenshot of VENTA_GC's script and share it with us. Maybe its result is 0 because of some filter made in its script.

Cheers

Re: Recovered lines : 0

MarioCenteno — Mon, 23 Jul 2018 17:44:13 GMT

The funny thing is that when I do the extraction of the data in toad, if there is data even when I showed them in qlik.

I think weird

Re: Recovered lines : 0

Thiago_Justen_ — Mon, 23 Jul 2018 19:42:21 GMT

Share with us this piece of script that I've mentioned above.

Re: Recovered lines : 0

BalaBhaskar_Qlik — Tue, 24 Jul 2018 08:49:00 GMT

Check the data availability of the first table in Data source, tweak the SQL script.

May be, if you're applying any calculations in SQL script, check that did you specified Group by Clause or not at the end.

Re: Recovered lines : 0

MarioCenteno — Tue, 24 Jul 2018 14:19:53 GMT

Hi!

Actually I am calculating the average of the sales of the last 3 months of the current month and then I compare it with the actual sale

Maybe that calculation is affecting me the data load.

Re: Recovered lines : 0

BalaBhaskar_Qlik — Tue, 24 Jul 2018 15:58:10 GMT

Can you post the, Specific script, using for this table?

Re: Recovered lines : 0

MarioCenteno — Tue, 24 Jul 2018 16:05:31 GMT

This is:

[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)) csmo_ant,sum(b.csmo_kwh) 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;

Re: Recovered lines : 0

Thiago_Justen_ — Tue, 24 Jul 2018 16:41:07 GMT

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!!

Re: Recovered lines : 0

MarioCenteno — Tue, 24 Jul 2018 18:53:44 GMT

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.

Re: Recovered lines : 0

BalaBhaskar_Qlik — Wed, 25 Jul 2018 05:28:42 GMT

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;

Re: Recovered lines : 0

MarioCenteno — Wed, 25 Jul 2018 13:55:01 GMT

Average of the last 3 months of sales.

Re: Recovered lines : 0

MarioCenteno — Wed, 25 Jul 2018 14:24:02 GMT

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?

Re: Recovered lines : 0

MarioCenteno — Wed, 25 Jul 2018 14:24:07 GMT

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?

Re: Recovered lines : 0

MarioCenteno — Wed, 25 Jul 2018 15:44:19 GMT

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

Re: Recovered lines : 0

BalaBhaskar_Qlik — Wed, 25 Jul 2018 15:51:05 GMT

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