Different results from same load-string?

[Anonymous]

Hello,

I have developed a dashboard for our company. However, I am encountering the following problem.

There is data coming from our company in Argentina. This data is located in 1 file, called "Argentina - New 2011 All Data.xls".

We also have a budget for next year, which is located in another file, called "Budget AR 2012".

There is a product we sold this year, which we will also sell in next year. This means it is in both files. Now here's a little part from the files:

As you can see, the number and the name are exactly the same in both files.

The lay-out for the cells are also the same ('Number' for the Product_number and 'General' for the Product_name).

I tie these to fields together in my script, adding a " - " in between and calling the filed [Material - Code & Name].

So you would get: "210794001205467 - PERSOFTAL L 1000KG."

Now I load these two files into Qlikview. Both files are loaded seperatly, and are concatenated seperatly into another table, like this:

(This is the table from our main office, which also contains products, different from the Argentina ones)
Product_7:
LOAD %MaterialNumber_Key, 
     [Material - Code & Name], 
     [Material Name], 
     [Line Desc 1]
FROM
MAKT_MaterialDescriptions_P60.qvd
(qvd);
(This is the table containing the 2011 data from Argentina)
//LE Concatenation
Concatenate (Product_7)
LOAD
%MaterialNumber_Key,
[Material - Code & Name],
[Material Name]
Resident ArgentinaTest; 
(This is the table containing the 2012 budget data from Argentina) 
//LE Budget Concatenate
Concatenate (Product_7)
Load
%MaterialNumber_Key,
[Material - Code & Name]
Resident Budget_LE;

Now if I tell Qlikview to show me a list box of the [Material - Code & Name] field and look for the product from Argentina, it shows me this:

As you can see, it is loaded into Qlikview 2 times, with a different number each time.

Does anyone have any idea what this could be?

I hope I explained it clear enough, if anyone has any questions, please ask!

Thanks in advance!

[dpietersz]

Could be something with the interpretation of the fields. Try to force a text interpretation with the functions TEXT().

[Anonymous]

I tried putting text() in front of the formula, like this:

text(Product_number) & ' - ' & text(Product_name) as [Material - Code & Name],

It didn't make any difference, still showing the two different numbers...

Another strange thing I noticed: I also load the 'Product_number' field as a key with the name %MaterialNumber_Key

When I check the listbox for that key-field, it does show only 1 number, the correct one...

[dpietersz]

Could you post a sample QlikView document? That will make it easier to help you.

[Anonymous]

How can I post a sample document?

I cannot give you all the files, that's huge data...

What would you need???

[dpietersz]

When you reply you can switch to the advanced editor (in the upper right corner of the editor). Than you can upload a file.

Just the QlikView file with a limited set of data. You can make a small selection in QlikView and than reduce the data (and keep the possible values).

Only post columns and data that are not a security risk for you.

[Anonymous]

Hmm, ok, this should do the trick I guess...

LOL, just found out I kept the password and username on it... Please use 'Diepen' and  'Diepen'.

Please click forward to Product (using the cycling icon) and then search in the box 'Material - Code & Name' for "Persoftal L 1000KG." and select the upper two.

As you can see, the problem lies with the data presented in the column 'KG', which is the data loaded for 2011 from the table ArgentinaTest.

The data presented in the column 'Budget KG' is the 2012 data, coming from the table Budget_LE.

You can see that the %MaterialNumber_Key is the same for both, which is strange, since the origin is the same as the number used in the Material - Code & Name...

[dpietersz]

It's hard to get to the bottom of the problem. There are alot of synthetic keys in the datamodel.

These synthetic keys are generated because of matching columns in more than one table. QlikView will generate automatic (synthetic) tables to keep their associative data model working. Because their is no control over the automatic generated synthetic tables it's hard to say where the problem lies.

If I would have to analyze this problem. I would start with loading only one table and see if you still have the problem. Do this for every table. If the problem doesn't occur, you can start step by step with your data model. Check after every step the data.

You have to make a datamodel without synthetic tables.

[Anonymous]

Hello Dimitri,

first of all: the keys are only there because I removed a lot of tables which have nothing to do with the problem at hand. The file I am using, is without keys, loops or whatever...

Strange thing is: if I concatenate the two files (2011 and 2012), it shows only 1 code, the right one. If I concatenate them seperatly from eachother with another table, it gives the 2 codes... Very strange thing it is...

[#10]

The problem will be solved once the synthetic keys are resolved.