Load error : Field not found

[#1]

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?

[amars]

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...

[#3]

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

[jagan · Accepted solution]

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.

[#5]

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?

[jagan]

Hi,

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

Regards,

Jagan.

[struniger]

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

[jagan]

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.

[#9]

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