topic Re: Join not recognizing Key field in QlikView

Join not recognizing Key field

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a fact table that I load a (Sample 0.25) in a resident table. in this SAMPLE TABLE I did some calculation and now I would like to apply the results back to the fact again. So I tried to join the SAMPLE TABLE in the fact but I get 2 rows per key field. One original and other with the results.

What I would like to have is to The FACT table with the results.

Does anyone knows the reason for not recognizing a key field?

Thanks.

Re: Join not recognizing Key field

sunny_talwar — Thu, 09 Jun 2016 19:30:58 GMT

Would you be able to share the code?

Re: Join not recognizing Key field

Anonymous — Thu, 09 Jun 2016 19:49:57 GMT

Yes!

PERCENTAGES

LOAD REGION,

NEW_RATE

FROM $(vReload)\PERCENTAGES.qvd (qvd);

//loading table with results

For a = 0 to NoOfRows('PERCENTAGES_NEW')-1

let vPERCENTAGE_NEW = replace((peek('NEW_RATE ',a,'PERCENTAGES'))/100,',','.');

let vPER_REG_N = peek('REGION',a,'PERCENTAGES');

//STD percentage

if '$(vPERCENTAGE_NEW )'=Null() or '$(vPERCENTAGE_NEW )'='0' or '$(vPERCENTAGE_NEW )'='' then

vPERCENTAGE_NEW = 0.2;

ENDIF

NEW_PROJ:

sample $(vPERCENTAGE_NEW ) load right([S/N],8) as [S/N],

REGION ,

MODEL,

'STANDARD' as [MODULE],

Date(Addmonths(T, 3)) as [START DATE],

Date(AddYears(addmonths(T,3),5))as [ END DATE],

'NEW RETENTION' as FLAG

resident WTable

where REGION = '$(vPER_REG_N)' and FLAG<>'YES' ;

drop Tables PERCENTAGES;

Re: Join not recognizing Key field

sunny_talwar — Thu, 09 Jun 2016 19:51:30 GMT

You mentioned a join, where exactly is the join taking place? Is there more script?

Re: Join not recognizing Key field

Anonymous — Thu, 09 Jun 2016 20:02:16 GMT

Sorry, there is more below:

STORE WTable into $(vLocale)\WTable.qvd (qvd);

drop table WTable;

TABLE_TABLE:

load *,

0 as TEST

resident NEW_PROJ;

drop table NEW_PROJ;

TABLE_JOIN:

join(TABLE_TABLE)

LOAD right([S/N],8) as [S/N],

MODEL,

[END DATE],

[START DATE],

[MODULE],

STATUS,

AGE

from $(vLocale)\WTable.qvd (qvd);

Re: Join not recognizing Key field

Anonymous — Thu, 09 Jun 2016 20:04:28 GMT

So,

the fields (END DATE, START DATE, MODULE and FLAG) from table NEW_PROJ has data different from WTable. I think it because of this why it is not working.

Re: Join not recognizing Key field

sunny_talwar — Thu, 09 Jun 2016 20:11:18 GMT

Can you post the whole script. I don't see the WTable created, but it is getting stored

Re: Join not recognizing Key field

Anonymous — Thu, 09 Jun 2016 20:57:56 GMT

Thanks Sunny!

I was trying to join with multiple fields in common. Thats why I was having two lines per S/N. Now I changed the filed's name and did an IF CLAUSE replacing ZERO values by those in the fields renamed.

All good now.