7 Replies Latest reply: Jan 22, 2012 5:57 AM by Miguel Angel Baeyens de Arce RSS

    SQL problem

    Dirk De Wispelaere

      Hi,

       

      In qlikview i have 2 tables:

      DataA208:
      LOAD Jaar,
           IDF,
           RX,
           CX,
           VV,
           KD,
           Doc,
           TCT,
           [Code station]
      FROM
      [..\..\CV-R-KD\A208\A208.xls]
      (biff, embedded labels, table is Sheet1$);Next i want to run a SQL on those tables:

       

      Then i want to run a SQL on those tables.

      The purpose, joining the data from both tables.

       

      LOAD;

      SQL

      SELECT RESIDENT DataA208.Jaar, RESIDENT DataA208.IDF, RESIDENT DataA208.RX, RESIDENT DataA208.CX, RESIDENT DataA208.VV, RESIDENT DataA208.KD, RESIDENT [Code stations MPP].StationRap

      FROM RESIDENT DataA208 LEFT JOIN RESIDENT DataMPPCodeStation ON RESIDENT DataA208.[Code station] = RESIDENT DataMPPCodeStation.[Code Station];

       

      Then i get the error, syntax error,missing operator in querry expression.

       

      What am I doing wrong?

      I'm pretty new in Qlikview

       

       

       

       

       

       

      DataTest208:

      DataMPPCodeStation:
      LOAD [Code Station], StationRap FROM
      [..\..\Code stations MPP.xls]
      (biff, embedded labels, table is Sheet1$);

        • Re: SQL problem
          Dirk De Wispelaere

          Modified question, my text was messed up.

           

          Hi,

          In qlikview i have 2 tables:

           

          DataMPPCodeStation:

          LOAD [Code Station], StationRap FROM
          [..\..\Code stations MPP.xls]
          (biff, embedded labels, table is Sheet1$);

           

          and

           

          DataA208:
          LOAD Jaar,
               IDF,
               RX,
               CX,
               VV,
               KD,
               Doc,
               TCT,
               [Code station]
          FROM
          [..\..\CV-R-KD\A208\A208.xls]
          (biff, embedded labels, table is Sheet1$);

           

           

          Then i want to run a SQL on those tables.

          The purpose, joining the data from both tables.

           

           

          DATATEST208:

          LOAD;

          SQL

          SELECT RESIDENT DataA208.Jaar, RESIDENT DataA208.IDF, RESIDENT DataA208.RX, RESIDENT DataA208.CX, RESIDENT DataA208.VV, RESIDENT DataA208.KD, RESIDENT [Code stations MPP].StationRap

          FROM RESIDENT DataA208 LEFT JOIN RESIDENT DataMPPCodeStation ON RESIDENT DataA208.[Code station] = RESIDENT DataMPPCodeStation.[Code Station];

           

          Then i get the error, syntax error,missing operator in querry expression.

           

          What am I doing wrong?

          I'm pretty new in Qlikview

           

            • Re: SQL problem
              Nicolas Stefaniuk

              Hi.

               

              I have never seen this syntax in fact. Are you sure that you can SQL with resident ? Try this code:

               

              DATATEST208:

              LOAD

              [Code station],

              Jaar,

              IDF,

              RX,

              CX,

              VV,

              KD

              RESIDENT DataA208;

               

              LEFT JOIN (DATATEST208)

              Load

              [Code station],

              StationRap

              RESIDENT DataMPPCodeStation;

               

              Remember that the syntax "table.field" can be used in QV only if you have declared QUALIFY before. Else you will just use "field" and the join is done implicitely on the synonyms.

                • Re: SQL problem
                  Miguel Angel Baeyens de Arce

                  Hi,

                   

                  nstefaniuk suggestion is correct. However, I'd rather use a mapping table. Depending on the size of the data set, JOINs in QlikView use a lot of disk space making the application bigger. The result, if the data model is correct, will be the same:

                   

                  DataMPPCodeStationMap:
                  MAPPING LOAD [Code Station], StationRap FROM
                  [..\..\Code stations MPP.xls]
                  (biff, embedded labels, table is Sheet1$);
                  
                  DataA208:
                  LOAD Jaar, 
                       IDF, 
                       RX, 
                       CX, 
                       VV, 
                       KD, 
                       Doc, 
                       TCT, 
                       [Code station],
                       ApplyMap('DataMPPCodeStationMap', [Code station]) AS StationRap
                  FROM
                  [..\..\CV-R-KD\A208\A208.xls]
                  (biff, embedded labels, table is Sheet1$);
                  

                   

                  Note that in QlikView field names and values are case sensitive, so [Code station] is not the same as [Code Station] (note the capital S in the second table).

                   

                  Hope that helps.

                   

                  Miguel

                  • Re: SQL problem
                    Dirk De Wispelaere

                    Hi,

                     

                    "Remember that the syntax "table.field" can be used in QV only if you have declared QUALIFY before. Else you will just use "field" and the join is done implicitely on the synonyms."

                     

                    How and where do i have to declare "QUALIFY"?

                     

                    Thx for the answers.