1 Reply Latest reply: Sep 17, 2015 1:58 AM by Vova AUSS RSS

    How load data with filter from other table?

      Pls, help me solve this task.

      I have DATA.TXT with IDDOC field wich has non-unique values.

      I have DATABASE.QVD with IDDOC field too (and non-unique values too).

      I need to load all data from DATABASE.QVD except those records, IDDOC that are present in DATA.TXT, and next append all records from DATA.TXT to result table. At end store result table to DATABASE.QVD

      If DATA.TXT is empty (only labels at first string), all data from DATABASE.QVD must be loaded.

       

      I have tried so, but could not:

      1. Load from TXT field IDDOC as ID_DOC to IDTable (for collect all new IDDOCs)

      2. Load from QVD with filter (how it do correct?! see below) to DataTable (for load all old, except with new IDDOCs)

      3. Add all data from TXT to DataTable (I dont know - is it correct or not. see below) (append to old data all new data)

      4. Store DataTable to QVD (for use at next time).

       

      IDTable:
      LOAD IDDOC as ID_DOC
      FROM [data.txt] txt, codepage is 1251, embedded labels, delimiter is ';', msq);
      
      DataTable:
      LOAD SOMEDATA1, SOMEDATA2, IDDOC
      FROM [DataBase.qvd] (QVD) Where Not Exists(IDDOC, peek('ID_DOC'));
      
      ADD LOAD SOMEDATA1, SOMEDATA2, IDDOC
      FROM [data.txt] txt, codepage is 1251, embedded labels, delimiter is ';', msq);
      
      STORE DataTable INTO [DataBase.qvd] (QVD);
      
      
      
      
      
      
      

       

      Test data DATA.TXT:

      somedata1;somedata2;iddoc

      1;2;123

      3;4;123

      5;6;987

       

      After first load we change some data with IDDOC=123 in DATA.TXT to something other, remove record with IDDOC=987, and add new record with IDDOC=999:

      somedata1;somedata2;iddoc

      10;20;123

      3;4;123

      30;40;999

       

      We must have at finish in DATABASE.QVD (comments in brackets):

      somedata1;somedata2;iddoc

      10;20;123 (from last DATA.TXT)

      3;4;123 (from last DATA.TXT)

      5;6;987 (from first DATA.TXT)

      30;40;999 (from last DATA.TXT)

       

      ps: Sorry for bad english, I use google translate. A week can not win such a petty problem.

      WBW.

        • Re: How load data with filter from other table?

          I found a solve by trial and error.


          qualify *;
          
          IF NOT isNull(fileSize('data.txt')) THEN
            newDataTable:
            LOAD somedata1, somedata2, IDD as IDDOC
            FROM [data.txt] (txt, codepage is 1251, embedded labels, delimiter is ';', msq);
          ELSE
            newDataTable:
            LOAD * INLINE [somedata1, somedata2, IDDOC];
          ENDIF
          
          IF NOT isNull(fileSize('DataBase.qvd')) THEN
            oldDataTable:
            LOAD somedata1, somedata2, IDD
            FROM [DataBase.qvd] (QVD);
          ELSE
            oldDataTable:
            LOAD * INLINE [somedata1, somedata2, IDD];
          ENDIF
          
          unqualify *;
          
          DataTable:
          LOAD
            oldDataTable.somedata1 as somedata1,
            oldDataTable.somedata2 as somedata2,
            oldDataTable.IDD as IDD
          RESIDENT oldDataTable WHERE Not Exists(newDataTable.IDDOC, oldDataTable.IDD);
          
          CONCATENATE (DataTable)
          LOAD somedata1, somedata2, IDD
          FROM [data.txt] (txt, codepage is 1251, embedded labels, delimiter is ';', msq);
          
          Drop Table newDataTable;
          Drop Table oldDataTable;
          
          STORE DataTable INTO [DataBase.qvd] (QVD);