Associate data from 2 factory codes

[#1]

Hi

I have  a report that looks at the sales of product by factory that supplied them.

The problem I have is that as a product is superseded we change the factory code of the old product so that it doesn't conflict with the new one. This report however needs to show the sales of the superseded product as well as the current one as being from the same supplier.

I have an excel spreadsheet which shows the link between the current factory code and the superseded factory code but I don't know how I am going to tell Qlikview to associate or merge the data so that it comes under one factory code.

For example factory code E003A is the newest factory code but it needs to be linked to E003 and 500003 so when someone is looking at the sales for 2014 for factory code E003A they can also see sales of products that have been superseded that now come under factory code E003 or 500003.

I have attached the qvw if that helps.

Thanks Steve

[jpenuliar]

hi Steve,

I was  going to suggest to create a Mapping table, but it seems you already have it done.

You would want to link fields Factory and FactoryCode so I suggest to Rename FactoryCode to Factory.

This way Qlikview will interpret both fields(Factory) as associated to each other.

[#3]

Hi Jonathan

I have tried that and whilst it has shown the link between the 2 factory codes if I look just at the associated factory code (the old factory code) then the products that are listed under that code alone are not showing when clicking on the primary factory code.

If I select E003A its old factory code is 500003.

As you can see from the attached images if I select E003A it doesn't show the products that are under factory code 500003 if I just select that for the same time frame.

Any thoughts?

Steve

[Ralf-Narfeldt]

If you do like this:

Add a preceding load to your Products: load

LOAD *, If(IsNull(LookUp('FactoryCode', 'OldFactoryCode', Factory, 'FactoryCodes')), Factory, LookUp('FactoryCode', 'OldFactoryCode', Factory, 'FactoryCodes')) As FactoryCode;

It adds a FactoryCode field to the Products table which contains the current FactoryCode. It's a lookup in the FactoryCodes table, it looks if the factory is an old code and returns the matching new FactoryCode if it is, if it's not an old code the existing Factory code is kept.

Of course, you'll have to use the FactoryCode now in your application

[#5]

Hi Ralf

I have just tried that and reloaded the script but it has not pulled in the products from the old factory code.

Have I put the preceding load in the right place?

Thanks

Steve

[Ralf-Narfeldt]

Yes, that looks right. I had to mock up some data as I can't reload your QVW (I don't have the data files needed).

FactoryCodes:

LOAD * INLINE [

FactoryCode,OldFactoryCode

A0019,100019

B010,200010];

Products:

LOAD *, If(IsNull(LookUp('FactoryCode', 'OldFactoryCode', Factory, 'FactoryCodes')), Factory, LookUp('FactoryCode', 'OldFactoryCode', Factory, 'FactoryCodes')) As FactoryCode;

LOAD * INLINE [

Factory,TSLCode

A0019,TRY1234

100019,Tgy689

B010,TTr98769

200010,Uyyi865];

You are looking at the FactoryCode field, not the Factory?

[#7]

Hi Ralf

I have tried selecting from the Factory code list rather than the factory list and I get the same results. These codes are still missing.

Thanks, Steve.

[jpenuliar]

Hi Steve,

If the Factory Name hasn't changed, you can probably use this field instead of Factory Code.

You will have a table with FactoryName,FactoryCode (you can probably extend with FactoryCodeAssignedDate etc..) ,similar to building table relationship in Database.

You can Add FactoryName in the Products Table

[#9]

Hi Jonathan

Forgive me for having a thick moment here but I don't understand what you're saying. The current factory codes are the alpha numeric codes and the old factory codes are the 6 digit ones. If the users of the report want to see what sales are for factory E003A for 2014 I need the report to show all products for E003A plus all codes that are represented by the old factory code 500003 in that time frame. I don't have the factory name included in this report just the code which is sufficient. To bring the factory name in as well would be another table which is unnecessary.

Thanks Steve.

[jpenuliar]

I think I had the other way around,

Factory should be linked to OldFactoryCode, your Chart dimension will be FactoryCode/

Does the Products table have historical record of current and previous Factory Code?