topic Different results from same load-string? in QlikView

Different results from same load-string?

Anonymous — Thu, 08 Dec 2011 09:11:35 GMT

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!

Different results from same load-string?

dpietersz — Thu, 08 Dec 2011 09:50:44 GMT

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

Different results from same load-string?

Anonymous — Thu, 08 Dec 2011 10:00:49 GMT

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...

Different results from same load-string?

dpietersz — Thu, 08 Dec 2011 10:51:04 GMT

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

Different results from same load-string?

Anonymous — Thu, 08 Dec 2011 11:01:35 GMT

How can I post a sample document?

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

What would you need???

Different results from same load-string?

dpietersz — Thu, 08 Dec 2011 11:11:29 GMT

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.

Re: Different results from same load-string?

Anonymous — Thu, 08 Dec 2011 11:28:10 GMT

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...

Re: Different results from same load-string?

dpietersz — Thu, 08 Dec 2011 12:53:58 GMT

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.

Re: Different results from same load-string?

Anonymous — Thu, 08 Dec 2011 12:56:45 GMT

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...

Different results from same load-string?

Thu, 08 Dec 2011 12:59:30 GMT

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

Re: Different results from same load-string?

dpietersz — Thu, 08 Dec 2011 13:11:55 GMT

I don't understand the 2 different ways of concatenating.

Could you post the 2 different scripting approaches you mean.

If you still have the problem without synthetic tables, than it has to be in one of the steps you do.

You can be sure of this if you make a new clean .qvw and load only one table at the time. My guess is that will show the correct data.

You could also try to see if there is a difference in the cell properties of the 2 excel files.

Re: Different results from same load-string?

Anonymous — Thu, 08 Dec 2011 13:18:03 GMT

1st Concatenate way:

- Load Table 2011 data

- Load Table 2012 data

- Load Table with HeadQuarter data

- concatenate Table 2011 data with Table HQ-data

- concatenate Table 2012 data with Table HQ-data

2nd Concatenate way:

- Load Table 2011 data

- Load Table 2012 data

- concatenate Table 2012 data with Table 2011 data

1st way gives the double data

2nd way only gives single data, as it should be...

Complicated for me to explain this clearly to you all I'm afraid 😞 Think I'll try the table for table approach you suggest...

Re: Different results from same load-string?

dpietersz — Thu, 08 Dec 2011 13:26:49 GMT

I beginning to understand it. Don't worry, it's always hard to explain these things via internet 😉

You can use the function "exit script;" between your load statements to see if everything is working as it should be.

Maybe QlikView automaticllay does a concatenation which you don't expect. If your load state has exactly the same column names as a previous load statement QlikView will concatenate it for you. If you don't want this you can use "noconcatenate load" instead of "Load".

Re: Different results from same load-string?

Anonymous — Thu, 08 Dec 2011 13:37:51 GMT

I reload once more, and everything works... I didn't change a thing...

This is very, very strange...

Well, thanks for the help Dimitri, much appreciated 🙂

Re: Different results from same load-string?

dpietersz — Thu, 08 Dec 2011 13:42:41 GMT

Your welcome!

Good luck!!