topic Problem loading resident table in QlikView

Problem loading resident table

Fri, 17 Feb 2012 14:37:41 GMT

Hi everyone!

im trying to run the script below but an error legend appear which saids

"Field not found - <AñoMes>

AsOf:

Load distinct AñoMes

Resident CALENDARIO_SUBSCRIPTIONS"

"Table not found

Left Join (AsOf)

Load AñoMes as AñoMesAsOf

Resident AsOf"

"Table not found

Inner Join (AsOf)

Load *

Resident AsOf

Where AñoMesAsOf >= AñoMes"

1) Script:

CALENDARIO_SUBSCRIPTIONS:

LOAD

Year(PeriodDate) & Num(Month(PeriodDate),'00') & Num(Day(PeriodDate),'00') as %Key_MODIFIED_SUBSCRIPTIONS,

Year(PeriodDate) as Año,

Month(PeriodDate) as Mes,

Day(PeriodDate) as Dia,

PeriodDate as Fecha,

DUAL(Month(PeriodDate) & '-' & Year(PeriodDate),

Year(PeriodDate) & Num(Month(PeriodDate),'00')) as AñoMes,

Week(PeriodDate) as Semana,

Ceil(Month(PeriodDate)/3) as Trimestre,

(Year(PeriodDate) * 12) + Num(Month(PeriodDate)) as LineaTemporal

RESIDENT TempCalendar;

AsOf:

Load distinct AñoMes

Resident CALENDARIO_SUBSCRIPTIONS;

Left Join (AsOf)

Load AñoMes as AñoMesAsOf

Resident AsOf;

Inner Join (AsOf)

Load *

Resident AsOf

Where AñoMesAsOf >= AñoMes;

thanks!

gerardo

Problem loading resident table

marcos — Fri, 17 Feb 2012 20:31:09 GMT

hi ,

i have tested your script with a previous table "TempCalendar" with a field PeriodDate and the script works correctly.

I am working with QlikView 10 SR4. What version of QV are you using?

Is probably that the problem is in the previous script.

Problem loading resident table

Fri, 17 Feb 2012 21:10:27 GMT

Hi Marcos,

can you attach me the *.qvw you made?

I dont know why but in one qvw the script run well but not in other. The script is exactly the same....

What do you mean "the problem is in the previous script"?

thank you!

Problem loading resident table

swuehl — Fri, 17 Feb 2012 22:55:21 GMT

I assume Marcos is talking about something like this:

TempCalendar:

LOAD

date(Makedate(2012)+recno()-1) as PeriodDate

AutoGenerate 366;

CALENDARIO_SUBSCRIPTIONS:

LOAD

Year(PeriodDate) & Num(Month(PeriodDate),'00') & Num(Day(PeriodDate),'00') as %Key_MODIFIED_SUBSCRIPTIONS,

Year(PeriodDate) as Año,

Month(PeriodDate) as Mes,

Day(PeriodDate) as Dia,

PeriodDate as Fecha,

DUAL(Month(PeriodDate) & '-' & Year(PeriodDate),

Year(PeriodDate) & Num(Month(PeriodDate),'00')) as AñoMes,

Week(PeriodDate) as Semana,

Ceil(Month(PeriodDate)/3) as Trimestre,

(Year(PeriodDate) * 12) + Num(Month(PeriodDate)) as LineaTemporal

RESIDENT TempCalendar;

AsOf:

Load distinct AñoMes

Resident CALENDARIO_SUBSCRIPTIONS;

Left Join (AsOf)

Load AñoMes as AñoMesAsOf

Resident AsOf;

Inner Join (AsOf)

Load *

Resident AsOf

Where AñoMesAsOf >= AñoMes;

The script also worked fine at my place using QV11. I also think that some script part preceding your snippet is causing the problem, or don't you have any statements prior the posted ones?

At least the TempCalendar load has to be done right?

If you are saying that the code runs well in another script, are you loading the same data? Have you checked the content of field AnoMes in Calendario_Subscriptions and of field PeriodDate in TempCalendar?

If possible, could you post a sample file?

Regards,

Stefan

Problem loading resident table

Sat, 18 Feb 2012 00:44:41 GMT

Thanks a lot for your answer Swuehl....i attached the qvw with data in the main question.

I think the problem is the way i load the calendar in the qvw.

In the qvw you will see a chart which has a metric with full accumulation check enable and the problem when you select some months because the full accumulation feature only include this some months and not all months in the history.

thanks

gerardo

Problem loading resident table

swuehl — Sat, 18 Feb 2012 13:19:12 GMT

You are using qualified field names:

QUALIFY *;

Unqualify '%Key_*';

That's the reason why you get

"Field not found - <AñoMes>

The field name you must reference in your AsOf Table is CALENDARIO_SUBSCRIPTIONS.AnoMes

Problem loading resident table

marcos — Sat, 18 Feb 2012 13:23:06 GMT

Hi Gerardo,

as i supposed the problem with your script is in the previous script.

The qualify function is affecting the field names, and the right name for AñoMes after reload the CALENDARIO_SUBSCRIPTIONS table is CALENDARIO_SUBSCRIPTIONS.AñoMes.

A possible solution for your script is this:

QUALIFY *;
Unqualify '%Key_*';

//1) Script:

CALENDARIO_SUBSCRIPTIONS:
LOAD
Year(PeriodDate) & Num(Month(PeriodDate),'00') & Num(Day(PeriodDate),'00')    as %Key_MODIFIED_SUBSCRIPTIONS,
Year(PeriodDate)                                                         as Año,
Month(PeriodDate)                                                        as Mes,
Day(PeriodDate)                                                            as Dia,
PeriodDate                                                                 as Fecha,
DUAL(Month(PeriodDate) & '-' & Year(PeriodDate),
Year(PeriodDate) & Num(Month(PeriodDate),'00'))                        as AñoMes,
Week(PeriodDate)                                                            as Semana,
Ceil(Month(PeriodDate)/3)                                                as Trimestre,
(Year(PeriodDate) * 12) + Num(Month(PeriodDate))                            as LineaTemporal
RESIDENT TempCalendar;

Unqualify *;

AsOf:
Load distinct CALENDARIO_SUBSCRIPTIONS.AñoMes
Resident CALENDARIO_SUBSCRIPTIONS;

Left Join (AsOf)
Load CALENDARIO_SUBSCRIPTIONS.AñoMes as AñoMesAsOf
Resident AsOf;

Inner Join (AsOf)
Load *
Resident AsOf
Where AñoMesAsOf >= CALENDARIO_SUBSCRIPTIONS.AñoMes;

Problem loading resident table

Sat, 18 Feb 2012 15:32:00 GMT

thank you very much guys! the script run great! there is a beer witing for you in argentina....

I need a last answer about this....

How can i do to use this in the "neto acum" metric intead of full accumulation feature.

Thanks!

Problem loading resident table

swuehl — Sat, 18 Feb 2012 16:10:57 GMT

For your subscriptos acum expression, try

=aggr(

rangesum( above(Count(DISTINCT{$<SUBSCRIPTIONS.is_subscribed = {'1'}, CALENDARIO_SUBSCRIPTIONS.AñoMes=>}SUBSCRIPTIONS.email)

-Count(DISTINCT{$<SUBSCRIPTIONS.is_subscribed = {'0'}, CALENDARIO_SUBSCRIPTIONS.AñoMes=>}SUBSCRIPTIONS.email),0,rowno()))

, CALENDARIO_SUBSCRIPTIONS.AñoMes)

and disable the full accumulation option in expression tab (set to 'no accumulation').

Problem loading resident table

marcos — Sat, 18 Feb 2012 16:31:36 GMT

great solution swuehl

Problem loading resident table

Sat, 18 Feb 2012 17:08:23 GMT

great! thank you very much to both!

Problem loading resident table

Sat, 18 Feb 2012 19:17:00 GMT

Hi Marcos and Swuehl,

i tried a lot of times to do the same with only subscriptions but i couldn't, i mean the full accumulation expression of

Count(DISTINCT SUBSCRIPTIONS.email) with out full accumulation expresion box enable....

could you please give me some advice?

Thanks

gerardo

Problem loading resident table

swuehl — Sat, 18 Feb 2012 19:52:52 GMT

Maybe something like

=aggr( rangesum( above(

Count({<CALENDARIO_SUBSCRIPTIONS.AñoMes=, SUBSCRIPTIONS.is_subscribed={0,1}>} DISTINCT SUBSCRIPTIONS.email)

,0,rowno()))

, CALENDARIO_SUBSCRIPTIONS.AñoMes)

Problem loading resident table

Sat, 18 Feb 2012 20:38:21 GMT

sorry....that give me the values of each month and not the accumulation....

some other idea?

Problem loading resident table

swuehl — Sat, 18 Feb 2012 20:56:01 GMT

Seems to work for me:

CALENDARIO_SUBSCRIPTIONS.AñoMes	Suscriptos acum	Altas	Bajas
	5099063	710482	63606
Nov-2010	2099	2083	16
Dic-2010	30201	27672	430
Ene-2011	71726	33214	8311
Feb-2011	78350	3560	3064
Mar-2011	94609	14891	1374
Abr-2011	116944	18778	3565
May-2011	136219	17557	1725
Jun-2011	170877	32803	1865
Jul-2011	236217	62693	2653
Ago-2011	325069	79175	9709
Sep-2011	399641	70567	4032
Oct-2011	536844	132006	5210
Nov-2011	618367	75774	5842
Dic-2011	704834	79046	7491
Ene-2012	776441	65571	6093
Feb-2012	800625	21576	2620

and if I select last few month:

CALENDARIO_SUBSCRIPTIONS.AñoMes	Suscriptos acum	Altas	Bajas
	3437111	367418	27042
Oct-2011	536844	132006	5210
Nov-2011	618367	75774	5842
Dic-2011	704834	79046	7491
Ene-2012	776441	65571	6093
Feb-2012	800625	21576	2620

Problem loading resident table

Sat, 18 Feb 2012 22:21:05 GMT

now is working.....thanks you!