joining 2 fields from different tables

[felcar2013]

hi,

i have two big tables CUSTOMERS (12 m. records) and TRANSACTIONS (24 million records). in customers i have the field %mainstorenumber__member__ID and in transactions the field %transaction__store__Id.

I need to make a query where i sum the sales for the last 365 days and where the %transaction__store__ID = %mainstorenumber__member__ID. for this i planned to create a flag, where "1" means that the transaction store was the main store, and "0" if this is not the case

I created a new table, joining both fields in that new table, which created some synthetic tables in the data model, but worked and got the numbers i needed. the problem is, when filtering, the dimensions numbers change. The model is not stable and wanted to know, how can i create the "%flagmainstore" field (below), without needing to create a new table. I tried with join load of the couple of  fields of the customer table i needed into the transaction table, but it did not work. where should i put my "IF" condition? (with the if is also very slow). I tried with set analysis, but i do not get the correct numbers

thanks

membermainstoresales:

LOAD %member__transaction__ID,
           %mainstorenumber__member__ID,
           member_NonMember

Resident member where member_NonMember = 'Member';

Right Join(membermainstoresales)
transactionmainstoresales:

LOAD %member__transaction__ID,
     %transaction__store__ID

Resident transaction;

mainstoresales:

NoConcatenate

LOAD        %member__transaction__ID,
           %mainstorenumber__member__ID,
           %transaction__store__ID,
           member_NonMember,
           if(%mainstorenumber__member__ID = %transaction__store__ID,1,0) as %flagmainstore

Resident membermainstoresales;

drop Table membermainstoresales;

[#2]

Why not to change your key field name, to have direct association (key with the same name) between CUSTOMERS and TRANSACTIONS?

For instance name %mainstorenumber__member__ID as %transaction__store__ID ....