topic Re: Load error : Field not found in QlikView

Load error : Field not found

Fri, 18 Oct 2013 02:55:18 GMT

Hi everyone, I have 2 tables, lets say its table A and B

in table A, I have a Date field which I loaded, and I renamed it to DateA

Load field1A, field2A, Date as DateA

from fileA

in table B, I need to load data, it has Date field as well but I want the Date in B to be greater (later) than DateA

Load field1B, field2B, Date as DateB

from fileB

where DateB > DateA

but I am getting field not found error (DateA)?

where exists seem to work when i compared field2A and field2B

Anyone know why?

Re: Load error : Field not found

amars — Fri, 18 Oct 2013 04:16:18 GMT

Hi Rinaldo,

Is there only one record present in Table A or do you wish to compare only the Max(DateA) with Table B.

If you wish to compare it with the Max(DateA) use peek function to retrieve the date in variable and use it to compare. Like

TableA: Load

field1A, field2A, Date as DateA

from fileA Asc Date;

Let vMaxDateA = Peek(DateA);

TableB:

Load field1B, field2B, Date as DateB

from fileB where Date > Date(vMaxDateA);

Thanks...

Re: Load error : Field not found

Fri, 18 Oct 2013 04:19:11 GMT

Thank you for your reply, just when I am in front of my computer!

I have actually more than one records in table A, each may contain different dates

Thanks in advance

Re: Load error : Field not found

jagan — Fri, 18 Oct 2013 04:19:24 GMT

Hi,

Try this script for this we need a column to join both the tables, I assumed that ID is a common field in the two tables, replace ID with your join column name

Table1:

Load

ID,

field1A, field2A, Date as DateA

from fileA;

Table2Temp:

Load

ID,

field1B, field2B, Date as DateB

from fileB;

LEFT JOIN (Table2Temp)

LOAD

ID,

DateA AS TempDateA

RESIDENT Table1;

Table2:

LOAD

ID,

field1B,

field2B,

DateB

FROM Table2Temp

WHERE DateB > TempDateA;

DROP TABLE Table2Temp;

Regards,

Jagan.

Re: Load error : Field not found

Fri, 18 Oct 2013 06:17:38 GMT

Hi Jagan, I tried your script

but when loading the final table (where the dates were being compared), it loaded continuously non stop

I should be expecting around 5000 results or less, but it's still going even up to the millions, I had to abort it

Do you know why?

Re: Load error : Field not found

jagan — Fri, 18 Oct 2013 06:35:46 GMT

Hi,

Can you attach some sample data? It would be easier to understand.

Regards,

Jagan.

Re: Load error : Field not found

struniger — Fri, 18 Oct 2013 06:43:45 GMT

Try this:

TableA:

Load

ID,field1A, field2A, Date as DateA

from fileA;

tmpMaxdate:

LOAD

num(max(DateA)) AS MaxDate

RESIDENT

TableA;

LET vMaxDate = peek('MaxDate', 0, 'tmpMaxdate');

DROP TABLE tmpMaxdate;

TableB:

Load field1B, field2B, Date as DateB

from fileB where Date > Date(vMaxDate);

Good luck

Stefan

Re: Load error : Field not found

jagan — Fri, 18 Oct 2013 07:44:46 GMT

Hi,

Is there any common fields in both tables, if there are common (join) fields this won't take much time, can you attached the sample script or file.

Regards,

Jagan.

Re: Load error : Field not found

Wed, 23 Oct 2013 04:58:27 GMT

Hi everyone, I am sorry for the late reply, we had bushfire in the surrounding area and had smoke problem.

Jagan, actually it was an error on my part, which I fixed shortly after I posted the question. I included the wrong field. It is now working properly, and I used your approach Jagan.

Yes, there is an ID that connects the two tables. I had to modify your approach here and there but it is generally the same.

Thank you very much to everyone that contributed, it allowed me to learn more about Qlikview

Regards,

Aldo