script help

[Anonymous]

Table A

ID            Date

1               1/2/2013

2               1/2/2013

3               1/2/2013

1               2/2/2013

2               2/2/2013

3               2/2/2013

1              3/2/2013

2               3/2/2013

3               3/2/2013   

Table B

ID                  Date

1               1/2/2013      

2               2/2/2013

3               3/3/2013

Result Table

ID                Date

1                    2/2/2013

1                    3/2/2013

2                    1/2/2013

2                    3/2/2013

3                    1/2/2013

3                    2/2/2013

I Tried outer join its not working

[jonathandienst · Accepted solution]

Hi

This will exclude all the items in Table B:

TableA:

LOAD ID, Date

Inline

[

          ID, Date

          1, 1/2/2013

          2, 1/2/2013

          3, 1/2/2013

          1, 2/2/2013

          2, 2/2/2013

          3, 2/2/2013

          1, 3/2/2013

          2, 3/2/2013

          3, 3/2/2013  

];

TableB:

LOAD ID & '.' & Date As ExcludeKey

Inline

[

          ID, Date

          1,  1/2/2013     

          2,  2/2/2013

          3,  3/2/2013

];

ResultTable:

NoConcatenate

LOAD * Resident TableA

Where not(Exists(ExcludeKey, ID & '.' & Date));

DROP Table TableA, TableB;

Regards

Jonathan

[Anonymous]

TableB:

LOAD ID & '.' & Date As ExcludeKey

for this Autonumber(ID,Date ) as ExcludeKey its correct or not

[jonathandienst]

If you want to autonumber the keys (useful if the number of exclude keys is very large), then use the following

TableA:
LOAD ID, Date
Inline
[
          ID, Date
          1, 1/2/2013
          2, 1/2/2013
          3, 1/2/2013
          1, 2/2/2013
          2, 2/2/2013
          3, 2/2/2013
          1, 3/2/2013
          2, 3/2/2013
          3, 3/2/2013  
];
TableB:
LOAD Autonumber(ID & '.' & Date, 'EKey') As ExcludeKey
Inline
[
          ID, Date
          1,  1/2/2013     
          2,  2/2/2013
          3,  3/2/2013
];
ResultTable:
NoConcatenate
LOAD * Resident TableA
Where not(Exists(ExcludeKey, Autonumber(ID & '.' & Date, 'EKey')));
DROP Table TableA, TableB;

Jonathan