join is not working

[#1]

hello all

here is my code :

a :
LOAD REFERENCEKEYID,
STATUS,
DATE_TIME ,
USER_NAME,
OPERATION
FROM
[.......QVD\M2R_GUI_COMMENT_20160905.qvd]
(qvd) ;

LOAD DATE_APPLI,
REPORTINGID,
SOURCENAME,
ACKTRANSACTIONID as REFERENCEKEYID,
SUPERVISORPARTYNAME,
FROM
[D.......QVD\VIP_REP_M2R_DTM_ACKTRANSACTION_20160905.qvd]
(qvd) ;

can you tell me why i lose the information of the seconde qvd. ( VIP_REP_M2R_DTM_ACKTRANSACTION_20160905.qvd]).

when i create the straight table for example i cannot see  in the same line  REFERENCEKEYID and SUPERVISORPARTYNAME. (no value for SUPERVISORPARTYNAME.)

i have tried the left join and right join and concatenate but it's not working.

i attached a scrrenshot.

thank in advance for your help.

[saimahasan]

Please check whether the field REFERENCEKEYID has the same data type...

[saimahasan]

is this field having the numbers as numbers or text??

[krishnacbe]

Hi,

If the Key Column is Text, then check for Case. Or try to replace the Field Name with UPPER(ACKTRANSACTIONID) as REFERENCEKEYID.

[effinty2112]

Hi ferhat,

               Take a look at the tableviewer and hover over the key field REFERENCEKEYID . If the subset ratios for this field in each table add up to 100% then there are no values for this field common to both tables.

Good luck

Andrew

[jonathandienst]

It looks like he REFERENCEKEYID values from the first table do not fully align withe the ACKTRANSACTIONID values you are loading in the second table. Either the value ranges do not intersect properly, or the format of the values is different. Try REFERENCEKEYID in a list box - if some values are left aligned and others are right aligned, then the one table is producing a numbers as text and the other as number.

Try loading like this:

LOAD Text(REFERENCEKEYID) as REFERENCEKEYID,

STATUS,

DATE_TIME ,

USER_NAME,

OPERATION

FROM

[.......QVD\M2R_GUI_COMMENT_20160905.qvd]

(qvd) ;

LOAD DATE_APPLI,

REPORTINGID,

SOURCENAME,

Text(ACKTRANSACTIONID) as REFERENCEKEYID,

SUPERVISORPARTYNAME,

FROM

[D.......QVD\VIP_REP_M2R_DTM_ACKTRANSACTION_20160905.qvd]

(qvd) ;

[#7]

hi,

REFERENCEKEYID and ACKTRANSACTIONID is the same data type (number)

regards

[#8]

hi,

i have tried this your suggestion but it's not working. the REFERENCEKEYID and ACKTRANSACTIONID are number format.

the request in oracle works fine

Regards

[Anil_Babu_Samineni]

Can you post application?

[#10]

hi Andrew,

the subset ratio in the table M2R_DTM_ACKTRANSACTION on the field REFERENCEKEYID is 0.76%