topic Re: join is not working in QlikView

join is not working

Wed, 07 Sep 2016 14:19:24 GMT

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.

Re: join is not working

saimahasan — Wed, 07 Sep 2016 14:23:22 GMT

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

Re: join is not working

saimahasan — Wed, 07 Sep 2016 14:23:56 GMT

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

Re: join is not working

krishnacbe — Wed, 07 Sep 2016 14:26:08 GMT

Hi,

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

Re: join is not working

effinty2112 — Wed, 07 Sep 2016 14:26:50 GMT

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

Re: join is not working

jonathandienst — Wed, 07 Sep 2016 14:27:18 GMT

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) ;

Re: join is not working

Wed, 07 Sep 2016 14:43:07 GMT

hi,

REFERENCEKEYID and ACKTRANSACTIONID is the same data type (number)

regards

Re: join is not working

Wed, 07 Sep 2016 14:48:12 GMT

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

Re: join is not working

Anil_Babu_Samineni — Wed, 07 Sep 2016 14:50:21 GMT

Can you post application?

Re: join is not working

Wed, 07 Sep 2016 14:52:29 GMT

hi Andrew,

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

Re: join is not working

effinty2112 — Wed, 07 Sep 2016 14:55:29 GMT

Hi Ferhat,

What is the subset ratio for this field in the other table?. If it is 99.24% then that would suggest that there are no common values for that field in the two tables.

Kind regards

Andrew

Re: join is not working

Wed, 07 Sep 2016 15:46:53 GMT

Hi,

i had a similar problem with number formatting. if text doesn't work try something like this:

a :
LOAD

'X-'&REFERENCEKEYID AS REFERENCEKEYID,
STATUS,
DATE_TIME ,
USER_NAME,
OPERATION
FROM
[.......QVD\M2R_GUI_COMMENT_20160905.qvd]
(qvd) ;

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