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

[#2]

Hi Jonathan

Product table has all products, their description, costs, factory code that they are produced at, case quantities, pallet quantities etc.

So for example if we have a code 1234 its factory code would be 500003 because it has been superseded. The new code would be 1234A and its factory code would be E003A. From the list box the user would select E003A and in the pivot table the user would see E003A as the factory code but would want to see the sales for 1234A and the sales for 1234 because this was superseded part way through the year and they would want the full years sales for that factory code including codes that have been superseded and were now under the old factory code.

Thanks Steve

[Ralf-Narfeldt]

I tried to replicate your data with some inline loads, not all of it, but a few fields in each table, and the joining fields. You can see in the attached document.

One thing with my solution is that the Factory codes: table needs to be loaded before you can do the lookup in the Products table, it was in my example, but I forgot to mention the importance of it.

So:

1. Move your Factory codes: table load to be the first table to load in the script, or at least before Products:

2. Add this preceding load to Products:

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

[#4]

Hi Ralf

Thanks for that and explaining why. I am still very much learning this so explanations as to why help me a lot. I have followed your instructions but the pivot table still does not update with the products that are now listed under the old factory code.

I have attached the updated qvw.

Steve.

[Ralf-Narfeldt]

OK, but you are still using the Factory field in the pivot table, and that will contain a new or old factory code.

You shouldn't do the rename to Factory here:

FactoryCodes:

LOAD FactoryCode as Factory,

     OldFactoryCode

it should be just

LOAD FactoryCode,

     OldFactoryCode

Then you can use the FactoryCode field in the pivot table. Products will contain:

FactoryCode - always the current FactoryCode

Factory - contains either the old or the new code

In fact, you can end the script by dropping the FactoryCodes table, as it served its purpose for lookup.

DROP TABLE FactoryCodes:

[#6]

Hi Ralf

Thanks again for explaining this. I have done as you said and reloaded but I still cannot see the codes that are on the old factory code. I have removed Factory from the pivot table to see if that made a difference but it hasn't.

Steve

[Ralf-Narfeldt]

OK, it's hard to replicate without the full data set as I can't reload.

There may be something missing in my limited Inload data.

[#8]

Hi Ralf

I have attached the full data set for you. Hope that helps.

Steve

[Ralf-Narfeldt · Accepted solution]

I added FactoryCode to the pivot table, and by selecting A0019 I think I can see data for both the old and the new (see Factory)

[#10]

Hi Ralf

Fantastic. I see what you have done with this now. Works a treat. Thank you for explaining as well as it helps me learn.

Steve