9 Replies Latest reply: Mar 21, 2012 11:53 AM by Fernando Augusto Schmitz RSS

    Left join inside loop

      Hi.

       

      I'm having trouble with the following script:

       

      LET StartYear = 2011;

      LET StartMonth = 1;

       

      FOR a=StartYear to 2011

       

      FOR m= StartMonth to 3

       

      OrcTemp:

      Load

          Ano,

          CDUNIDADEORCAMENTARIA,

          CDSUBACAO,

          CDFONTERECURSO,

          CodigoEspecificacao,

          ValorInicial,

          $(m) as Mes

      Resident

          Dotacao

      Where

          Ano=$(a);

       

      Left Join

      LOAD

          Ano,

          CDUNIDADEORCAMENTARIA,

          CDSUBACAO,

          CDFONTERECURSO,

          CodigoEspecificacao,

          ValorAlterado

      Resident

          Movimentacao

      Where

          Month(Data) <= $(m);

       

      next

      next

       

      Qlikview is creating a different table with the suffix "-N" for each iteration. When I try remove the left join, everything works fine. Any ideas about what am I doing wrong?

        • Left join inside loop

          p.s. The expected result was all rows into only one table(OrcTemp).

            • Left join inside loop
              Michael Solomovich

              Try this:
              Create blank table OrcTemp (without any records) before loops.  You can use load inline or load autogenerate 1.
              In the loop, where you now have table name, replace it with CONCATENATE (OrcTemp);
              In the join, add table name: LEFT JOIN (OrcTemp).

               

              See if it helps.

                • Left join inside loop

                  Doesnt worked. The problem persisted. When the Left Join is executed a -N table is created the same way as the previous solution.

                    • Left join inside loop
                      Michael Solomovich

                      This is strange.  Can I you show the script after the change?

                      (Or was it response to Celambarasan?)

                        • Left join inside loop

                          LET StartYear = 2011;

                          LET MonthYear = 1;

                           

                          OrcTemp:

                          Load

                              2009 as Ano,

                              1 as CDUNIDADEORCAMENTARIA,

                              1 as CDSUBACAO,

                              1 as CDFONTERECURSO,

                              1 as CodigoEspecificacao,

                              1 as ValorInicial,

                              1 as Mes

                          AutoGenerate 1;

                           

                          FOR a=AnoInicio to 2011

                           

                          FOR m= If(a=AnoInicio,MesInicio,1) to 3

                           

                          OrcTemp:

                          Load

                              Ano,

                              CDUNIDADEORCAMENTARIA,

                              CDSUBACAO,

                              CDFONTERECURSO,

                              CodigoEspecificacao,

                              ValorInicial,

                              $(m) as Mes

                          Resident

                              Dotacao

                          Where

                              Ano=$(a);

                             

                          Left Join

                          LOAD

                              Ano,

                              CDUNIDADEORCAMENTARIA,

                              CDSUBACAO,

                              CDFONTERECURSO,

                              CodigoEspecificacao,

                              ValorAlterado

                          Resident

                              Movimentacao

                          Where

                              Month(Data) <= $(m);

                           

                          next

                           

                          next

                            • Left join inside loop
                              Michael Solomovich

                              Not what I meant.  This is the correct one (notice bold):

                               

                              LET StartYear = 2011;

                              LET MonthYear = 1;


                              OrcTemp:

                              Load

                                  2009 as Ano,

                                  1 as CDUNIDADEORCAMENTARIA,

                                  1 as CDSUBACAO,

                                  1 as CDFONTERECURSO,

                                  1 as CodigoEspecificacao,

                                  1 as ValorInicial,

                                  1 as Mes

                              AutoGenerate 1;


                              FOR a=AnoInicio to 2011


                              FOR m= If(a=AnoInicio,MesInicio,1) to 3


                              CONCATENATE (OrcTemp)

                              Load

                                  Ano,

                                  CDUNIDADEORCAMENTARIA,

                                  CDSUBACAO,

                                  CDFONTERECURSO,

                                  CodigoEspecificacao,

                                  ValorInicial,

                                  $(m) as Mes

                              Resident

                                  Dotacao

                              Where

                                  Ano=$(a);

                                 

                              Left Join (OrcTemp)

                              LOAD

                                  Ano,

                                  CDUNIDADEORCAMENTARIA,

                                  CDSUBACAO,

                                  CDFONTERECURSO,

                                  CodigoEspecificacao,

                                  ValorAlterado

                              Resident

                                  Movimentacao

                              Where

                                  Month(Data) <= $(m);


                              next


                              next

                    • Left join inside loop
                      Celambarasan Adhimulam

                      Hi,

                           Try with this

                          

                      LET StartYear = 2011;

                      LET StartMonth = 1;

                       

                      FOR a=StartYear to 2011

                       

                      FOR m= StartMonth to 3

                       

                      OrcTemp:

                      Load

                          Ano,

                          CDUNIDADEORCAMENTARIA,

                          CDSUBACAO,

                          CDFONTERECURSO,

                          CodigoEspecificacao,

                          ValorInicial,

                          $(m) as Mes

                      Resident

                          Dotacao

                      Where

                          Ano=$(a);

                      NoConcatenate

                      Orc:

                      Load

                                *

                      Resident OrcTemp;

                      Left Join

                      LOAD

                          Ano,

                          CDUNIDADEORCAMENTARIA,

                          CDSUBACAO,

                          CDFONTERECURSO,

                          CodigoEspecificacao,

                          ValorAlterado

                      Resident

                          Movimentacao

                      Where

                          Month(Data) <= $(m);

                      Drop Table OrcTemp;

                      next

                      next

                       

                      Celambarasan