Key Field not working as expected

[gerhardl]

Hello,

I am loading from 2 TXT files and am using a key field as follows:

LOAD     Field1,

               Field2,

               [Appl No],

               Field4

FROM TEXT FILE 1.

LOAD     Field5

               Field6,

               [APPL NO] as [Appl No],

               Field7

FROM TEXT FILE 1.

I feel like I've done this a million times and the key field will then join the two tables. Yes when I create a table box it is not working and shows the data from the two tables in 2 separate rows:

Please see the attached image.

[sujeetsingh]

Hi,

Please specify left join it will work.HOPE

[gerhardl]

Hi,

I did try that but with the same result.

Right after posting the question though, I remembered that I had a nother key field in both my tables:

filebasename() as File

I commented this out of the script and then everything worked fine. Not sure why this is a problem.

[sujeetsingh]

It may be that due to this nulls appeared..

[ariel_klien]

Hi,

try to do this:

LOAD     Field1,

               Field2,

               text([Appl No]) as [Appl No],

               Field4

FROM TEXT FILE 1.

LOAD     Field5

               Field6,

               text([APPL NO]) as [Appl No],

               Field7

FROM TEXT FILE 1.

see if that works.

BR

Ariel

[gerhardl]

Hi,

This did not work. Issue is definitely with the key field "File" - when I removed this it worked.

I changed this in the following way:

In the first load:

mid(filebasename(),45,11) as File_Date,

In the second load:

mid(filebasename(),32,11) as File_Date,

This gives me the datestamp in the different filenames, and it works fine. Still no idea why the normal filbasename() would cause issues... but as long as it works now.

Thanks all.