8 Replies Latest reply: Jun 18, 2012 5:56 AM by Hampus von Post RSS

    Loading data in wrong table

      Does anyone know why the following is happening?

       

      Explain.png

      I think I am loading data into NoCommunicationTmp but it is actually inserting all of that data into another table (SmokeDetectionTmp).

      I am out of ideas why this is occurring, any help is welcome.

        • Re: Loading data in wrong table
          Hampus von Post

          Hi,

           

          You've only got one dot (.) in the FROM PATH.

           

          So the file cannot be loaded. Try changing it to "..\" instead.

           

          Regards,

          Hampus

            • Re: Loading data in wrong table

              What do you mean, because I can't follow. Where should the dot be ?

               

              Here is an extract of the screenshot:

               

              BlowOutsTmp:

              LOAD

                  timestamp#(DATE_TIME) as DateTime,

                  LINENUMBER as LineNumber,

                  ORIGIN as Origin

              FROM

              [$(CSV_PATH)\*.nsp.blowOut.csv]

              (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

               

               

              Execute cmd /c del /Q $(CSV_PATH)\*.nsp.blowOut.csv;

               

               

              /******************************************************************************/

              /* Load the times there is no communication with the lines.                   */

              /******************************************************************************/

               

               

              NoCommunicationTmp:

              LOAD * FROM [$(CSV_PATH)\NoCommunication.qvd](qvd);

               

               

              NoCommunicationTmp:

              LOAD

                   timestamp#(DATE_TIME) as DateTime,

                   LINENUMBER as LineNumber

              FROM

              [$(CSV_PATH)\*.nsp.noCommunication.csv]

              (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

               

               

              Execute cmd /c del /Q $(CSV_PATH)\*.nsp.noCommunication.csv;

               

               

               

               

               

              The CSV_PATH is defined as follows:

               

              SET CSV_PATH = .\Data\Aerocom

               

              Message was edited by: uzg_developer

                • Re: Loading data in wrong table
                  Hampus von Post

                  Hi again,

                   

                  My bad. Saw the ".\" part in the NoCommunicationTmp From bit as an incorrect form of "..\" which moves up one folder. Nothing incorrect there.

                   

                   

                  Have you checked that the NoCommunication.qvd file exists in the the path .\Data\Aerocom\NoCommunication.qvd? Could be the error.

                   

                   

                  Regards,

                  Hampus

                    • Re: Loading data in wrong table

                      The main problem is that it loads the NoCommunicationTmp data into the SmokeDetectionTmp...

                       

                      Here is the definition of SmokeDetectionTmp. Yes I know that they have the same structure, but the data is completly different so I can't merge these.

                       

                       

                      SmokeDetectionTmp:

                      Load * FROM [$(CSV_PATH)\SmokeDetection.qvd](qvd);

                       

                      SmokeDetectionTmp:

                      Load

                      timestamp#(DATE_TIME) as DateTime,

                      LINENUMBER as LineNumber

                      From

                      [$(CSV_PATH)\*.nsp.smokeDetection.csv]

                      (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

                       

                      Execute cmd /c del /Q $(CSV_PATH)\*.nsp.smokeDetection.csv;

                        • Re: Loading data in wrong table
                          Johannes Sunden

                          Here's your problem:

                          Since the tables have the exact same structure they will be automatically concatenated.

                          Look up automatic concatenation in the manuals.

                          You'll need to either use NoConcatenate or alter the field names so you don't have an identical field structure between the tables.

                          • Re: Loading data in wrong table
                            Hampus von Post

                            Hi,

                             

                            If that's the problem then you should add the "noconcatenate" before the load statement of the NoCommunicationTmp sections, but you'll get synthetic keys (more keys linking the data).

                             

                            So to avoid that you should instead encapsulate the NoCommunicationTmp with a qualify *;

                            The qualify will cause the field names from the table NoComm.. to be prefixed with the tablename.

                            In other words will LineNumber from NoComm.. be called NoCommunicationTmp.LineNumber, and it will no longer be linked or concatenated with the SmokeDetectionTmp.

                             

                            Remember to add the unqualify *; when you're done with the NoCommunicationTmp.

                             

                            Regards,

                              • Re: Loading data in wrong table

                                The NoConcatenate causes my load with * (star) to create hundreds/thousands of different tables, that kind of sucks :-)

                                  • Re: Loading data in wrong table
                                    Hampus von Post

                                    That's because you define NoCommunicationTmp more than once.

                                     

                                    If you comment out the first NoCommunicationTmp load statement the NoCommunicationTmp tablename won't be increment with each file loaded..

                                     

                                    Or you could add the following prior to the first NoCommunicationTmp load:

                                     

                                    NoCommunicationTmp:

                                    noconcatenate LOAD * INLINE [

                                        LineNumber

                                    ];

                                     

                                    But you'll still get the synthetic keys so you should rename the fieldnames a bit

                                     

                                    With the code above you have to remove the "noconcatenate" part from the two NoCommunicationTmp tables following