Duplication with Cross Table

[#1]

Hello,

I have a problem with duplication in my file.  I have a file that was loaded as a cross table that has POS sales information for January through May.  This file also includes On hands for the customer.  My on hands are being duplicated by every month instead of just showing the one number.  So if on hands were 5 of an item and there was POS sales in the 5 months, then the on hands will be shown as 25 in the expression.  I tried to concatatenate with Item #, Date, and On hand, but the file shows errors with the date.  Any help is appreciated.

Thanks,

[er_mohit]

Have you tried Distinct after load

like

load distinct

A.B,C

from table;

hope it helps

[#3]

I have tried that below is what my script looks like.

POS_2013:

CrossTable([Month 2013], [2013 POS], 13)

LOAD

On_Hand,

[Item Buyer],

     Department,

     Class,

     Subclass,

     [Item ID],

     UPC,

     SAP as Material,

     Status,

     Item,

     VPN,

     [2013 Unit Cost],

     [O/O] as [On_Order],

     [001/2013],

     [002/2013],

     [003/2013],

     [004/2013],

     [005/2013]

    

FROM

,

[2013 Unit Cost],

[2013 POS],

[On_Order],

[Item ID] & [On_Hand] & [Month 2013]as OH_KEY,

[Month 2013],

[2013 Unit Cost]*[2013 POS] as COGS2013,

    ApplyMap ('Date_Map',[Month 2013],0) AS Date,

    On_Hand

Resident POS_2013;

Drop Table POS_2013;

[er_mohit]

Try this

POS_2013:

CrossTable([Month 2013], [2013 POS], 13)

LOAD  Distinct

On_Hand,

[Item Buyer],

     Department,

     Class,

     Subclass,

     [Item ID],

     UPC,

     SAP as Material,

     Status,

     Item,

     VPN,

     [2013 Unit Cost],

     [O/O] as [On_Order],

     [001/2013],

     [002/2013],

     [003/2013],

     [004/2013],

     [005/2013]

    

FROM

,

[2013 Unit Cost],

[2013 POS],

[On_Order],

[Item ID] & [On_Hand] & [Month 2013]as OH_KEY,

[Month 2013],

[2013 Unit Cost]*[2013 POS] as COGS2013,

    ApplyMap ('Date_Map',[Month 2013],0) AS Date,

    On_Hand

Resident POS_2013;

Drop Table POS_2013;

[#5]

Thanks, this loaded without an error.  I am now trying to figure out how to use the OH_Key to match up with the On_Hand dimension and only load the one number instead of duplicating everything.  I created a Link Table with OH_Key and On_Hand as dimensions, but I get synthetic keys and it does not fix the problem.