Duplicate a table if a field has specific characters

[#1]

Hi experts,

Basically, I'm pulling many small files and putting them into 1 large table, making them unique by using FileName and a Field to make a Key.

[Text] is the same for every record in each File. It shows up as either

A,

B,

or AB

Here's my Script

LOAD

     Filename() as [File],

     @1 as [ID],

     @2 as [TEXT],

     Filename()&@2&@1 as [Key]

FROM

(txt, codepage is 1252, no labels, delimiter is ';', msq);

Here's Example Data

File1

ID     TEXT

1          A

2          A

3          A

4          A

File2

ID     TEXT

6          AB

7          AB

8          AB

9          AB

If it has AB in it to split these records into 2. So basically pull the entire file with all A's, then pull it again with all B's.

File2

ID     TEXT

6          A

7          A

8          A

9          A

6          B

7          B

8          B

9          B

My [Key] file should make sure there are no duplicates in this case, since there can't be duplicate file names.

Any help would be greatly appreciated. Thanks

[#2]

Schedule_Data:

LOAD

Filename() as [Schedule File],

@1 as [Trip],

@3,

@7 as DepTime,

@11,

len(@11) as Length

FROM

(txt, codepage is 1252, no labels, delimiter is ';', msq)

Where @3 > 0;

  FinalTable:

  LOAD *,

  [Schedule File]&Wkday1&[Trip] as [Key];

  LOAD *,

  Mid(@11, IterNo(), 1) as Wkday1

  Resident Schedule_Data

  While IterNo() <= Length;

  DROP Table Schedule_Data;

  DROP Field @11;

  RENAME FIELD Wkdy1 to Wkdy;

Other_Table:

LOAD *,

[ORIGIN TIME],

[File]&Wkdy2&[TRIP] as [Key],

(lookup('DepTime','Key',Key,'FinalTable'))  -  (Timestamp(Timestamp#([ORIGIN TIME],'h:mm:ss') ,'hh:mm')) as CalcTime

FROM

(txt, codepage is 1252, embedded labels, delimiter is ';', msq)

Error shows up as

Field not found - <Key>

Other_Table:

[sunny_talwar]

Give this a try:

Other_Table:

LOAD *,

[ORIGIN TIME],

[File]&Wkdy2&[TRIP] as [Key],

(lookup('DepTime','Key',[File]&Wkdy2&[TRIP], 'FinalTable'))  -  (Timestamp(Timestamp#([ORIGIN TIME],'h:mm:ss') ,'hh:mm')) as CalcTime

FROM

(txt, codepage is 1252, embedded labels, delimiter is ';', msq)

or

Other_Table:

LOAD *,

          (LookUp('DepTime','Key',[File]&Wkdy2&[TRIP], 'FinalTable'))  -  (Timestamp(Timestamp#([ORIGIN TIME],'h:mm:ss') ,'hh:mm')) as CalcTime;

LOAD *,

[ORIGIN TIME],

[File]&Wkdy2&[TRIP] as [Key]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ';', msq)