topic check on coding in QlikView

check on coding

qlikhans — Fri, 14 Jan 2011 22:09:13 GMT

Anyone sees a mistake in this code?

it results in: Field not found - <CRM5.contact.name>

Let vConnection = 'CRM5.contact Left Join SuperOffice.CRM5.udcontactsmall On CRM5.contact.userdef_id=CRM5.udcontactsmall.udcontactSmall_id Left Join CRM5.udlist On CRM5.udcontactsmall.long01=CRM5.udlist.UDList_id Left Join CRM5.associate On CRM5.contact.associate_id=CRM5.associate.associate_id Left Join CRM5.country On CRM5.contact.country_id=CRM5.country.country_id Left Join CRM5.business On CRM5.contact.business_idx=CRM5.business.business_id Left Join CRM5.category On CRM5.contact.category_idx=CRM5.category.Category_id';

LOAD CRM5.contact.name as client,

CRM5.country.name as country;

SELECT

CRM5.contact.name,

CRM5.country.name

From $(vConnection);

appreciate the help

kindrgrds

Hans

check on coding

Fri, 14 Jan 2011 22:15:23 GMT

LOAD

CLIENT ,

COUNTRY;

SELECT

CRM5.contact.name AS CLIENT ,

CRM5.country.name AS COUNTRY

From $(vConnection);

Please check if this works .

check on coding

hector — Fri, 14 Jan 2011 22:17:52 GMT

I think the problem is this line



LOAD CRM5.contact.name as client,
CRM5.country.name as country

because QlikView is not making the difference between the name from the table contact and the name from the table country.

In Sql you can have this because internally the name will be table.field, but qlikview doesn't work in this way

Change your script like this



LOAD
contact_name as client,
country_name as country;
SELECT
CRM5.contact.name as contact_name,

CRM5.country.name as country_name

From $(vConnection);

Anyway, you can check this removing the load statement, and using the pure sql query

Also, check how the driver retrieves the field names, for example in Oracle it doesn't matter if you put the aliases in lowercase, it convert them in upper case, so sometimes QlikView gives you an error because you are retrieving "field_name", but oracle returns "FIELD_NAME"

Rgds

check on coding

qlikhans — Fri, 14 Jan 2011 23:37:33 GMT

Hi priyadarshini

it works! thank you

bst rgrds

Hans

check on coding

qlikhans — Fri, 14 Jan 2011 23:40:19 GMT

Hi Hector,

the answer priyadarshini gave works out fine. I did not test out yours, but it will probably also.

Thanks

bst rgrds, Hans