2 Replies Latest reply: Mar 1, 2018 3:22 AM by Sandeep Hamelink RSS

    Appending Tables

    Paul Burger

      Hi,

       

      First post with an issue that is probably very simple but is driving me mad. I have searched the forums (and Google) for the answer but i cannot find it. Odd really, as it is a fundamental part of any data of any data control. Nevertheless, i just cannot figure out how to append new data to an existing data. I either end up losing the data of overwriting the data (I suspect deleting the table and creating a new one).

       

      What I have is a series of report delivered daily, with a time stamp in the report name. What I would like is to import the latest report into the table for further trend analysis.

       

      This is what I have (that does not work)

       

      Let DaysBack=1;

      Let StartDate=date(Today())-DaysBack;

      Let FolderYear = Year(StartDate);

      Let FolderMonth = num(Month(StartDate),00);

      Let FolderDay=num(Day(StartDate),00);

      Let FolderName='PROJECT'&' '& FolderYear&'-'&FolderMonth&'-'&FolderDay;

      Let DirectoryP1GSM='lib://Company_Local/'&FolderName&'/*GSM_PROJECT_P1.txt';

      //create path name to file for import (previous day)

       

      TempTable:

      LOAD * FROM [$(DirectoryP1GSM)](txt, codepage is 28591, embedded labels, delimiter is '\t', msq,table is [GSM_P1]);

      //import new data into temporary table

       

      GSM_P1:

      concatenate

      LOAD * Resident TempTable;

      //append temporary table to main table (GSM_P1)

       

      Drop Table TempTable;

      //lose temporary table and free up memory

       

       

      What I end up with is a loss of all tables!!

      Is should be noted the structure (headers etc) of each imported file is exactly the same.

       

      HELP!!

       

      Thanks,

       

      Paul