How load data with filter from other table?

Mon, 14 Sep 2015 04:57:12 GMT

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?

Thu, 17 Sep 2015 05:58:35 GMT

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);

topic How load data with filter from other table? in QlikView

How load data with filter from other table?

Re: How load data with filter from other table?