Mixing InputFiles

[#1]

Hi,

In the example below I try to mix different input files.
From new files only new lines may be selected. When I use “WHERE NOT EXISTS (KEY)” nothing of file
B will be selected. In my opinion the second line of file B should be selected.
That’s what I want.

  //=====EXAMPLE=========================================================================
:
LOAD * INLINE [
DATE, TICKET, LINE, SHOPCODE, QTY, KEY
05-06-2013, 100, 1, SH1, 10, SH1_100_1
05-06-20...



















;

CONCATENATE LOAD
      DATE,
     TICKET,
     LINE,
     SHOPCODE,
     QTY,
      KEY
RESIDENT
WHERE NOT EXISTS (KEY);

DROP TABLE ;
DROP TABLE ;

[#2]

The command "exists" determines whether a specific field value exists in a specified field of the data loaded so far, not only in the last table.

Change your script like this:

[SALES]:

NOCONCATENATE LOAD

      DATE,

     TICKET,

     LINE,

     SHOPCODE,

     QTY,

     KEY as KEY2

RESIDENT ;

CONCATENATE LOAD

      DATE,

     TICKET,

     LINE,

     SHOPCODE,

     QTY,

     KEY as KEY2

RESIDENT

WHERE NOT EXISTS ('KEY2',KEY);

[rbecher · Accepted solution]

Hi Jan,

the problem is that the keys already exists in table A/B. To solve this you have to rename the fields:

:
LOAD * INLINE [
DATE, TICKET, LINE, SHOPCODE, QTY, KEY_A
05-06-2013, 100, 1, SH1, 10, SH1_100_1
05-06-2013, 100, 2, SH1, 10, SH1_100_2
05-06-2013, 101, 1, SH1, 10, SH1_101_1
05-06-2013, 100, 1, SH2, 10, SH2_100_1
];

:
NOCONCATENATE LOAD * INLINE [
DATE, TICKET, LINE, SHOPCODE, QTY, KEY_B
05-06-2013, 100, 1, SH1, 20, SH1_100_1
05-06-2013, 102, 1, SH1, 10, SH1_102_1
];

[SALES]:
NOCONCATENATE LOAD
      DATE,
     TICKET,
     LINE,
     SHOPCODE,
     QTY,
      KEY_A as KEY
RESIDENT ;

CONCATENATE LOAD
      DATE,
     TICKET,
     LINE,
     SHOPCODE,
     QTY,
      KEY_B as KEY
RESIDENT 
WHERE NOT EXISTS (KEY, KEY_B);

DROP TABLE ;
DROP TABLE ;

- Ralf

[#4]

Hi,

Try using DISTINCT in your script and remove NOT EXISTS while concatenating as follows:

[SALES]:

NOCONCATENATE LOAD
      DATE,
    TICKET,
    LINE,
    SHOPCODE,
    QTY,
      KEY
RESIDENT ;

CONCATENATE LOAD DISTINCT
      DATE,
    TICKET,
    LINE,
    SHOPCODE,
    QTY,
      KEY
RESIDENT

Hope this helps!

[amars]

check attached file.

Thanks

[swuehl]

Since you loaded all KEY values in tables A and B, the KEY exists for all values when reloading the table B in you concatenate Load.

Either Load your data directly with the last two load statements (no preloading of field values), or use exists like this:

:

LOAD * INLINE [

DATE, TICKET, LINE, SHOPCODE, QTY, KEY

05-06-2013, 100, 1, SH1, 10, SH1_100_1

05-06-2013, 100, 2, SH1, 10, SH1_100_2

05-06-2013, 101, 1, SH1, 10, SH1_101_1

05-06-2013, 100, 1, SH2, 10, SH2_100_1

];

:

NOCONCATENATE LOAD * INLINE [

DATE, TICKET, LINE, SHOPCODE, QTY, KEY

05-06-2013, 100, 1, SH1, 20, SH1_100_1

05-06-2013, 102, 1, SH1, 10, SH1_102_1

];

[SALES]:

NOCONCATENATE LOAD

      DATE,

     TICKET,

     LINE,

     SHOPCODE,

     QTY,

      KEY,

      KEY as EXISTKEY

RESIDENT ;

CONCATENATE LOAD

      DATE,

     TICKET,

     LINE,

     SHOPCODE,

     QTY,

      KEY

RESIDENT

WHERE NOT EXISTS (EXISTKEY,KEY);

DROP TABLE ;

DROP TABLE ;

DROP FIELD EXISTKEY;

[Anonymous]

Jan,

You got nothing because all values of KEY exist in B already.  Your example will work if you make a small change:

[SALES]:
NOCONCATENATE LOAD
      DATE,
     TICKET,
     LINE,
     SHOPCODE,
     QTY,
      KEY,
     KEY as KEY1
RESIDENT ;

CONCATENATE LOAD
      DATE,
     TICKET,
     LINE,
     SHOPCODE,
     QTY,
      KEY
RESIDENT
WHERE NOT EXISTS (KEY1,KEY);
DROP FIELD KEY1;
...

[jagan]

Hi,

Instead of reloading the tables again simply use this

:

LOAD

*

INLINE

[

:

NOCONCATENATE

LOAD

*

INLINE

[

WHERE

NOT

EXISTS

(KEY

)

;

Regards,

Jagan.

[#9]

Hey Mate,

Please see attached. I am actually not sure why your previous expression did not work and if someone could explain this behaviour with NOCONCATENATE INLINE LOADS, I would be grateful too

Cheers,

Byron

[#10]

[SALES]:

LOAD * INLINE [

DATE, TICKET, LINE, SHOPCODE, QTY, KEY

05-06-2013, 100, 1, SH1, 10, SH1_100_1

05-06-2013, 100, 2, SH1, 10, SH1_100_2

05-06-2013, 101, 1, SH1, 10, SH1_101_1

05-06-2013, 100, 1, SH2, 10, SH2_100_1

];

CONCATENATE LOAD * INLINE [

DATE, TICKET, LINE, SHOPCODE, QTY, KEY

05-06-2013, 100, 1, SH1, 20, SH1_100_1

05-06-2013, 102, 1, SH1, 10, SH1_102_1

]

WHERE NOT EXISTS (KEY);

The INLINE loads create all kind of synthetic keys.

Above code will give you the result you're looking for.