Join Problem

[#1]

I have a table that contains sales data that I need to report on.  The table has fields such as item, qty, and amount.

The item in the file might contain data that is considered a return item.  If it is a return there would be an 'R' in front of the item.

There is also a cross-reference file that contains the 'R' numbers and what the actual item should be.

The user doesn't want to see 'R' items in the table but wants the pricing for the 'R' items to be rolled into the actual item.

Not sure how to load this in since Item would be used 3 times; how can I get around this??? basically the alternate item needs to somehow point back to the actual item and pick up the amount.

I'll give an example of how the data would look in the sales file:

Item	Qty	Amount
1132516551	5	35.5
1132516553	2	16.75
1132516555	1	26.35
R1132516553	-1	-8.42

So there is another file(alternate_file) that would contain the R item and what the actual item is:

Item                    Alt_Item

R1132516553     1132516553

So when I set up the pivot table to report on the sales data then I only want to see 1132516553 with amount being $8.33 because the R item gets rolled in with actual item which is 1132516553 based on the alternate_item file.

Note: I can't simply take the 'R' off the item because there are some that have completely different numbers so I must use the alternate_item file to find out what the actual item is. 

sales:

item,

qty,

amount

alternate_item:

item,

actual_item

[swuehl]

Try a mapping approach:

MAP:

MAPPING LOAD

     item,

     actual_item

FROM alternate_item;

Sales:

LOAD

     applymap('MAP', item) as item,

     qty,

     amount

FROM sales;

Then using item as dimension and sum(qty) and sum(amount) as expression should return what you want.

[#3]

If I use the mapping approach for Item what happens to the item that is in sales??? I would still need to pull all of the items that are in sales since this is the primary file.

Not all of the items that are in sales will be in this alternate file; only the 'R' items will be in the alternate.

[swuehl]

If the item is not be found in the mapping table MAP (as I've understood, this will happen for all items that are not part of RXXXX items), its value will just be piped through the applymap() function as default value, so no data should be lost.

[Joaquin_Lazaro]

Hi  :

I guess you have

LOAD ITEM

You should try

LOAD If (Left(ITEM, 1) = 'R', Mid(ITEM, 2), ITEM) as ITEM

Then you take off all the initial R of your items code

Hope this helps you

Joaquín