Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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
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;
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
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