0 Replies Latest reply: Apr 22, 2010 9:50 AM by Peter Scheurich RSS

    Mysterious phenomenon with CONCATENATE

      Hi,

       

      I have a question on the "concatenate" statement.

       

      The idea behind the following code is to process data partitioned for different periods $(a) and in the end put them together in one table "TabResult".

       

      Here is a very dumbed-down example:

       

       

       


      For a = 1 to 2
      TabA:
      Load f1
      ,f2
      ,f3
      FROM tabA.csv
      Where f1 = $(a)
      ;

      TabB:
      Load f1
      ,f2
      ,f4
      From tabB.csv
      Where f1 = $(a)
      ;

      NoConcatenate
      TabC:
      Load f1
      ,f2
      ,f3
      Resident TabA ;

      Concatenate (TabC)
      Load f1
      ,f2
      ,f4
      Resident TabB ;

      Drop Table TabA, TabB ;

      If $(a) = 1 then
      Set vConCat = 'NoConcatenate TabResult' ;
      Else
      Set vConCat = 'Concatenate (TabResult)' ;
      End If ;

      // If table not exists, create it, otherwise append the data (concatenate)
      $(vConCat)
      Load f1
      ,f2
      ,sum(f3) as f3
      ,sum(f4) as f4
      Resident TabC
      Group By f1, f2
      ;

      Drop Table TabC ;
      Next


       

      What's happening now is, that only the data from periode 1 (a=1) exist at the end, but not the data from period 2. That's because the data for period 2 are concatenatet to TabC instead of TabResult (you can see that during the execution at the status window). And as TabC is dropped, the data get lost. If you are looking at the LogFile you can see that with periode 2 the script is right: "Concatenate (TabResult) Load …."

       

      As TabC and TabResult have the same structure, I can understand that this will happen if you don't use the concatenate statement. But as it is used explicit, I think it works wrong.

       

      I know how to solve it (use a dummy column in on of the tables TabC or TabResult so that the structure is different). But what I want to know is, if somebody else got this problem.

       

      My opinion is, that it is a bug, but perhaps there is an good explanation why it works in this way. But as it is easy to solve, it's not a heavy thing.

       

      Greetings

      Peter