Joining rows on different fields dependent on whether populated

[#1]

Company	Customer	Product	Reduction %
1	24		40
1	23		4
1	25		10
2		Hat	5
2		Shoe	50

I have a list of reductions for clothing items in different company's. The problem is the reduction for 1 company for all items for a particular customer but for the other company it is for different products regardless of the customer.

I need to join this to my data only when the field is not null for example lines 1-3 would join on company and customer while lines 4-5 would join on company and product. Does anyone know how I can do it without hardcoding these joins. My data is slightly more complicated and has 6 fields that may be populated so not as simple as just bringing the table in twice.

Note: If the cell is blank I want it to join to my data for example, if the customer field is blank I expect all customers to get that discount who meet the other criteria

[sunny_talwar]

I guess you need to share the table to which it is getting joined to. Are there two tables or is there one table it is getting joined to? Can you share few rows from the table it needs to join to?

[#3]

This is an example of my data in the DB:

Company	Area Code	Customer Number	Product	Sales
2	10	10027	Handbag	1000
2	10	10027	Hat	50
1	25	10091	Hat	500
1	90	10071	shoes	600

In this example

Row 2 would get the 5% discount

Row 3 would get the 10% discount

The other rows do not meet the criteria

[sunny_talwar]

May be like this:

Table:

LOAD *,

  AutoNumber(Company&If(Flag = 'Customer', Customer, Product)) as Key;

LOAD Company,

  If(Len(Trim(Customer)) > 0, Customer) as Customer,

  If(Len(Trim(Product)) > 0, Product) as Product,

  [Reduction %],

  If(Len(Trim(Customer)) > 0, 'Customer', 'Product') as Flag;

LOAD * INLINE [

    Company, Customer, Product, Reduction %

    1, 24, , 40

    1, 23, , 4

    1, 25, , 10

    2, , Hat, 5

    2, , Shoe, 50

];

FactTable:

LOAD * INLINE [

    Company, Customer Number, Area Code, Product, Sales

    2, 10, 10027, Handbag, 1000

    2, 10, 10027, Hat, 50

    1, 25, 10091, Hat, 500

    1, 90, 10071, shoes, 600

];

Left Join(FactTable)

LOAD DISTINCT Company,

  Flag

Resident Table;

FinalFactTable:

LOAD *,

  AutoNumber(Company&If(Flag = 'Customer', [Customer Number], Product)) as Key

Resident FactTable;

Left Join (FinalFactTable)

LOAD Key,

  [Reduction %]

Resident Table;

DROP Tables FactTable, Table;

[#5]

Hi Sunny that does work on the simple example but my data has more options such as area code and period and any combination of them being filled out would make up the key. Unfortunately it is not just customer or product. It can sometimes be both as well or could be neither but for a particular area code

[sunny_talwar]

There are two ways to go about this

1) You provide a more comprehensive sample

2) Try to re-engineer my code to fit your needs

Best,

Sunny