15 Replies Latest reply: Feb 18, 2012 5:21 PM by gerardo.pauza RSS

    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

        • Problem loading resident table
          Marcos Gomez

          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

              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
                  Stefan Wühl

                  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

                      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
                          Stefan Wühl

                          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 Gomez

                            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;