Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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)