topic Re: Associate data from 2 factory codes in QlikView

Associate data from 2 factory codes

Thu, 26 Mar 2015 12:46:57 GMT

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

Re: Associate data from 2 factory codes

jpenuliar — Thu, 26 Mar 2015 13:42:04 GMT

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.

Re: Associate data from 2 factory codes

Thu, 26 Mar 2015 14:02:34 GMT

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

Re: Associate data from 2 factory codes

Ralf-Narfeldt — Thu, 26 Mar 2015 14:42:00 GMT

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

Re: Associate data from 2 factory codes

Thu, 26 Mar 2015 14:52:24 GMT

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

Re: Associate data from 2 factory codes

Ralf-Narfeldt — Thu, 26 Mar 2015 14:58:25 GMT

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?

Re: Associate data from 2 factory codes

Thu, 26 Mar 2015 15:09:17 GMT

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.

Re: Associate data from 2 factory codes

jpenuliar — Thu, 26 Mar 2015 15:38:24 GMT

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

Re: Associate data from 2 factory codes

Thu, 26 Mar 2015 16:03:31 GMT

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.

Re: Associate data from 2 factory codes

jpenuliar — Thu, 26 Mar 2015 16:17:59 GMT

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?

Re: Associate data from 2 factory codes

Thu, 26 Mar 2015 16:34:11 GMT

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

Re: Associate data from 2 factory codes

Ralf-Narfeldt — Fri, 27 Mar 2015 07:50:39 GMT

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;

Re: Associate data from 2 factory codes

Fri, 27 Mar 2015 08:32:56 GMT

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.

Re: Associate data from 2 factory codes

Ralf-Narfeldt — Fri, 27 Mar 2015 08:53:42 GMT

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:

Re: Associate data from 2 factory codes

Fri, 27 Mar 2015 09:06:30 GMT

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

Re: Associate data from 2 factory codes

Ralf-Narfeldt — Fri, 27 Mar 2015 09:27:24 GMT

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.

Re: Associate data from 2 factory codes

Fri, 27 Mar 2015 09:32:37 GMT

Hi Ralf

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

Steve

Re: Associate data from 2 factory codes

Ralf-Narfeldt — Fri, 27 Mar 2015 09:45:52 GMT

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)

Re: Associate data from 2 factory codes

Fri, 27 Mar 2015 09:55:50 GMT

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