problem using inner join.

Tue, 17 Nov 2009 13:15:19 GMT

hello,

i am getting problem in perfoming inner join for the following code.

LOAD "SALE_LOCN_CODE",
"SALE_DOC_TYPE",
"SALE_DOC_#",
"SALE_AMND_#",
"SALE_SRNO_#",
"SALE_FREE_FLAG",
"SALE_ITEM_CODE" ;

SQL SELECT *
FROM "NEWTON_BI1"."MKG_SALE_D" where substr("SALE_ITEM_CODE",2,2) in ('01','02','03','04','05');

LOAD "ITEM_ITEM_CODE",
"ITEM_ITEM_NAME",
"ITEM_IMP_FLG",
"ITEM_UOM_CODE",
"ITEM_BDG_ITEM",
"ITEM_SL_CODE" ;

SQL SELECT a."ITEM_ITEM_NAME"
FROM "NEWTON_BI1"."STR_ITEM_M" a INNER JOIN "NEWTON_BI1"."MKG_SALE_D" b
ON a."ITEM_ITEM_CODE"= b."SALE_ITEM_CODE" ;

the error it displays on reload is :-

Field not found - <ITEM_ITEM_CODE>
SQL SELECT a."ITEM_ITEM_NAME"
FROM "NEWTON_BI1"."STR_ITEM_M" a INNER JOIN "NEWTON_BI1"."MKG_SALE_D" b
ON a."ITEM_ITEM_CODE"= b."SALE_ITEM_CODE" .

please suggest the solution.

is there any other way to get ."ITEM_ITEM_NAME" depending upon the common "ITEM_ITEM_CODE" and ."SALE_ITEM_CODE" .

problem using inner join.

Tue, 17 Nov 2009 13:29:37 GMT

The error is because of your sql statement which only returns "ITEM_ITEM_NAME" where as in your load you have all other fields.

You could just load the item master and rename the item code field to join back to sales data.

LOAD "SALE_LOCN_CODE",
"SALE_DOC_TYPE",
"SALE_DOC_#",
"SALE_AMND_#",
"SALE_SRNO_#",
"SALE_FREE_FLAG",
"SALE_ITEM_CODE" ;

SQL SELECT *
FROM "NEWTON_BI1"."MKG_SALE_D" where substr("SALE_ITEM_CODE",2,2) in ('01','02','03','04','05');

LOAD

"ITEM_ITEM_CODE" AS "SALE_ITEM_CODE",

"ITEM_ITEM_NAME",

"ITEM_IMP_FLG",
"ITEM_UOM_CODE",
"ITEM_BDG_ITEM",
"ITEM_SL_CODE" ;

SQL SELECT *
FROM "NEWTON_BI1"."STR_ITEM_M" ;

Hope this helps

Thanks

Tariq

topic problem using inner join. in QlikView

problem using inner join.

problem using inner join.