topic Re: Duplication with Cross Table in QlikView

Duplication with Cross Table

Thu, 06 Jun 2013 13:10:20 GMT

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,

Re: Duplication with Cross Table

er_mohit — Thu, 06 Jun 2013 13:13:12 GMT

Have you tried Distinct after load

load distinct

A.B,C

from table;

hope it helps

Re: Duplication with Cross Table

Thu, 06 Jun 2013 13:22:53 GMT

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;

Re: Duplication with Cross Table

er_mohit — Thu, 06 Jun 2013 13:27:48 GMT

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;

Re: Duplication with Cross Table

Thu, 06 Jun 2013 13:56:44 GMT

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.