topic Re: Basic stripting question in QlikView

Basic stripting question

datanibbler — Wed, 04 Sep 2013 13:50:38 GMT

Hi,

this is seemingly a very trivial question - I just come across this for the first time.

In one Dashboard that I had finished about halfway, I now have to integrate data from a new source: An exact mirror_image of the database for another plant.

- To later be able to select one plant or the other (or both), I have included a plant_key into the tables.

- I have given this different names in the tables so that I don't end up with a lot of synthetic keys.

- Now, when I use the same fields I have in the "old" version, the keys are not unique anymore.

<=> Associating tables where the key is not unique in either table is never a good idea in my experience.

=> So, do I have to rename the existing keyfields (so they are not associated anymore) and build new ones, including the plant_key (that's the only thing I can think of as a reason)? Won't that influence the possibility of later selecting plants?

Thanks a lot!

Best regards,

DataNibbler

Re: Basic stripting question

swuehl — Wed, 04 Sep 2013 13:55:16 GMT

Not sure if I can follow.

If I assume correctly what you are trying to achieve, I would Load both tables into a concatenated table, adding a field Source to describe the source of the data, and maybe force selection of one source in the Dashboard.

Re: Basic stripting question

jpapador — Wed, 04 Sep 2013 13:59:12 GMT

If the two databases are the same you could concatenate the tables. If you'd still like to select the different plants on the primary key of the tables you could create a key that distinguishes the two:

Table1:

'PlantA' & PrimaryKeyField

Field1,

Field2,

etc

Concatenate (Table1)

LOAD

'PlantB' & PrimaryKeyField,

Field1,

Field2,

etc

This way you would still have one field for each dimension instead of an old and a new and a way to distinguish the two plants. You could even take it a step further and create a plant field:

If(Left(PrimaryKeyField, 5) = 'PlantA', 'Plant A', 'Plant B') as Plant

Re: Basic stripting question

kouroshkarimi — Wed, 04 Sep 2013 14:02:05 GMT

If I'm understanding you correctly, I often have similar issues. All I would do is keep the same field names in both sets and use a new field (e.g. plant_key) which is unique to each one. So you could have:

Field 1, Field 2, plant_key

xxx, xxxx, Plant_1

xxx, xxxx, Plant_2

Let QlikView concatenate the two tables and use plant_key to differentiate between the sets.

Re: Basic stripting question

jpapador — Wed, 04 Sep 2013 14:06:21 GMT

That is correct. To simplify is you could do

Plant A as Plant_Key in the first table and

Plant B as Plant_Key in the second table. That way both Tables will have the same field names while concatenating and you can also use the Plant_Key field to distinguish between the two.

Re: Basic stripting question

datanibbler — Wed, 04 Sep 2013 14:08:34 GMT

Hi jpapador,

I see I omitted a few things.

- I do concatenate the tables - as the structure must be the same, why create two tables.

<=> The problem is slightly different:

- Apparently, some IDs repeat in the other plant

=> That is why after adding the new data, the keys are not unique anymore.

By creating a compound key including the plant_key and renaming the former keyfields (to get them out of the way and avoid synthetic keys), one of my tables (I have three tables in my data_model currently) is back to a "primary key". That is unique if I remember correctly, so that's all right.

<=> In the other table I have to connect, the key is not unique anymore ("key", was "Perfect key") and creating a compound key incl. the plant does not help matters here.

Thanks a lot!

Best regards,

DataNibbler

Re: Basic stripting question

datanibbler — Wed, 04 Sep 2013 14:10:57 GMT

Hi jpapador,

that's what I'm doing.

What's worrying me is the fact that I cannot create a unique key anymore - strangely, in the old version of the Dashboard, it works - after a reload, the keys are still unique, so the base data can't have changed - so the error must lie with what I have done.

Re: Basic stripting question

datanibbler — Thu, 05 Sep 2013 10:54:15 GMT

OK,

this is solved I guess.

I just had a problem concerning the quality of the keys (Perfect key/ Primary key/ Key). As I thought, I had renamed something and some formulas were not working anymore.

A bit of logical thinking and retracing my steps solved the problem.

Now the quality of the keys is what it was before.

Thanks anyway!

Best regards,

DataNibbler