Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load error : Field not found

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?

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

8 Replies
amars
Specialist
Specialist

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

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

Hi,

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

Regards,

Jagan.

struniger
Creator
Creator

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
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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