Join not recognizing Key field

[Anonymous]

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.

[sunny_talwar]

Would you be able to share the code?

[Anonymous]

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

next

drop Tables PERCENTAGES;

[sunny_talwar]

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

[Anonymous]

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

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous · Accepted solution]

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.