Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
swuehl
MVP
MVP

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

View solution in original post

15 Replies
marcos
Partner - Contributor III
Partner - Contributor III

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
Author

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

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
Author

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

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

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
Author

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

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

great solution swuehl