Left-outer join problem

Thu, 20 Jan 2011 18:35:07 GMT

I've tried a number of different attempts to get this but to no avail. Here's a summary:

we have a project which can have 0,1 or many related records(call them reports)

I would like to show in one table all the customers and whether they have 0 / 1 / many reports (plus some basic fields to go with it.)

We can then add a simple list box to allow the user to filter on those projects with reports and those that don't.

The approach as I see it is:

1) build a small, single field table with the project id's

2) do a left-outer-join to the reports table - this will list all projects and whether they have a report or not

Now I managed to get this to work. But I then want to reload all the data again and check the 'report.date' field. If it contains a value, set a Boolean - Y, else N. Then I want to use this flag to filter the record. But it's this last bit that doesn't want to run without an 'cannot recognize field' error.

I have both tables loaded already (from *.qvd files.)

I then do:

reporttemp:

load

projectid

resident projects;

left outer join(reporttemp)

load *

resident reports;

I then try:

finaltable:

load *,

if(reportdate <> null, 'Y','N')

resident reporttemp;

<but it doesn't recognize the 'reportdate' field, or any field from the reporttemp table.> If I look at the list of available project fields from a sheet object, the table name has an unexpected prefix (reporttemp-1.) or something like this.

Has anyone tried to build something similar and have an alternative approach, or know what I'm doing wrong here?

Thanks,

Simon

Left-outer join problem

Thu, 20 Jan 2011 18:54:43 GMT

Hi Simon,

Did you tried just Left Join, instead of Left Outer join ?

Does any sample column/field exist in Projects and Reports table ?

Doest reportdate field exist in the reporttemp ?

And you can also change the If condition by "IF(Not isNull(reportdate),'Y','N')".

If you can post the sample qvw, i can be very clear about the problem.

Regards,

R.Srinivasan

topic Left-outer join problem in QlikView

Left-outer join problem

Left-outer join problem