Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Problem loading resident table

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

1 Solution

Accepted Solutions
MVP
MVP

Problem loading resident table

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

15 Replies
marcos
New Contributor III

Problem loading resident table

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.

Not applicable

Problem loading resident table

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!

MVP
MVP

Problem loading resident table

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

Not applicable

Problem loading resident table

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

MVP
MVP

Problem loading resident table

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
New Contributor III

Problem loading resident table

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;

Not applicable

Problem loading resident table

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!

MVP
MVP

Problem loading resident table

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
New Contributor III

Problem loading resident table

great solution swuehl