Exclude a list from Load script

[#1]

Hi,

Hope you could help. 

How do I exclude a certain list of IDs from loading?

I have the following script:

LOAD [Id],

     [TimeStamp],

     [Total Count],

     [Info1]

     [Info2]

     [New ID]

FROM

(txt, utf8, embedded labels, delimiter is ',', msq);

I would like to exclude a list of [New ID], stored in C:\newID.csv]

THe file contains about 3million records, not "," delimited.  Just a list of IDs. 

Could you pls help? THanks in advance.

[tresesco]

a quick idea (may be a better one is there) :

Temp:

LOAD [Id],

     [TimeStamp],

     [Total Count],

     [Info1]

     [Info2]

     [New ID]

FROM

(txt, utf8, embedded labels, delimiter is ',', msq);

LEFT Join

Load [New ID] as [Id],

        'Y' as Flag

From C:\newID.csv;

Final:

Load *,

          '1' as Jnk

Resident Temp WHERE Flag<>'Y' ;

Drop Table Temp;

Drop Field Jnk, Flag;

Hope this helps.

[tresesco]

Sorry, you have to use LEFT JOIN instead of join.

[#4]

I have 2 IDs in my original file, [id] and [New ID].  how do i left join?

I would like to load the data where [NewID] in source file is same as [NewID] in my new criteria list.

Also, what is the flag?

[tresesco]

i should have noticed that. please find the changes in the earlier post in bold.

[#6]

Load [New ID] as [Id],

        'Y' as Flag

From C:\newID.csv;

Final:

Load *,

          '1' as Jnk

Resident Temp WHERE Flag<>'Y' ;

Drop Table Temp;

Drop Field Jnk, Flag;

   Q1: Do i use join or left join?

Q2:  I do not have Flag field in my file. all i have is a list of new id, not separated by commas.\

How should the script be like?

[#7]

To be sure, the newID field in main file, is combined from the ID+Timestamp

Directory;

Temp:

LOAD [Id],

     [TimeStamp],

     [Total Count],

     [Info1]

     [Info2]

     [Id] &'-'& [Timestamp] as [New ID]

FROM

(txt, utf8, embedded labels, delimiter is ',', msq);

Load [New ID]

        'Y' as Flag

From C:\newID.csv;

Final:

Load *,

          '1' as Jnk

Resident Temp WHERE Flag<>'Y' ;

Drop Table Temp;

Drop Field Jnk, Flag;

----------------------------------------------

I encounter this error:

Field not found - NewID

LEFT Join

Load imsiTime,

        'Y' as Flag

From

----------------------------------------------

To be sure, the formats are as below:

Source file:

Id	Timestamp	Total Count	Info1	Info2
1	8am
2	8am
3	8am

NewID

1-8am

2-8am

3-8am

My final file should only output 1-8am and the parameters

[#8]

I think i have solved my join statement.

Now i have this error:

Table not found

FINAL:

Load *,

          '1' as Jnk

Resident Temp WHERE Flag<>'Y'

Can someone pls help?

[#9]

What about Where Not Exists.

Please see attached Example.

Hope this helps.

[#10]

is it possible to copy the script?

So sorry because i am using a personal edition.