Problem loading resident table

[#1]

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

[marcos]

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.

[#3]

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!

[swuehl]

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

[#5]

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

[swuehl]

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

[marcos]

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;

[#8]

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!

[swuehl · Accepted solution]

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').

[marcos]

great solution swuehl