topic Key Field not working as expected in QlikView

Key Field not working as expected

gerhardl — Tue, 11 Jun 2013 11:25:38 GMT

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.

Re: Key Field not working as expected

sujeetsingh — Tue, 11 Jun 2013 11:27:49 GMT

Hi,

Please specify left join it will work.HOPE

Re: Key Field not working as expected

gerhardl — Tue, 11 Jun 2013 11:30:48 GMT

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.

Re: Key Field not working as expected

sujeetsingh — Tue, 11 Jun 2013 11:32:58 GMT

It may be that due to this nulls appeared..

Re: Key Field not working as expected

ariel_klien — Tue, 11 Jun 2013 11:49:48 GMT

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.

Ariel

Re: Key Field not working as expected

gerhardl — Tue, 11 Jun 2013 12:08:13 GMT

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.