topic Re: Key Field for CrossTable in QlikView

Key Field for CrossTable

Tue, 25 Nov 2014 22:32:45 GMT

Hey All,

I did a crosstable load for maintenance plans info. After making it a crosstable, the %plansid key field duplicates over records to cover, so the data is all out of whack ( it took me adding several other dimensions to the 'plans' before figuring this out).

As you can see in the attached screenshots, I have played around a bit with autonumber to no avail. Is there a way to create a unique key to cover all possibilities of CompanyID, Plan Type, EXP date?

Note that I will also have to apply a map for the plan type names and then add other dimensions like company info and plan details -- I don't forsee this being an issue as long as I have this key field.

Thanks!

Re: Key Field for CrossTable

Tue, 25 Nov 2014 23:06:32 GMT

I noticed you used the Autonumber function in the first image. Have you tried adding CompanyID, PlanType and ExpDate? I believe if you do so it will create a number for every different combination these 3 have (which is what I suppose you want when you talk about covering all possibilities of these 3).

Else you could concatenate them, that'll create a unique key for each possible combination.

Re: Key Field for CrossTable

Wed, 26 Nov 2014 00:19:59 GMT

Thanks for the reply.

I tried -- autonumber(%CompanyID) + autonumber() + autonumber() as %PlansKey,

As well as a couple variations and no luck. What syntax would you use?

To give a bit more detail:

- Currently if I do a count(companyId) count(plantype) count(expdate) all three give me the same value. Expdate has the largest number of rows.

- Even when I click on one company, the count(company) is based on how many plans that company has.

- There was a key, but after doing a crosstable, it is no longer applicable.

I am thinking that I can’t just concatenate them, as in theory, the new plansID key would need to be reused.

Maybe there is a simpler or better answer?

Re: Key Field for CrossTable

Wed, 26 Nov 2014 01:13:43 GMT

The syntax I was talking about is Autonumber(field1&field2&field3&field4). Try that and tell me what happened.

Re: Key Field for CrossTable

Wed, 26 Nov 2014 01:19:47 GMT

Hey Eduardo --

That's exactly what i needed to jog my memory. Thanks so much!

It wound up being:

autonumber(companyid&'-'&plantype) <-- realized that combo gives me the unique plans.

Thanks!

Re: Key Field for CrossTable

Wed, 26 Nov 2014 02:12:39 GMT

Anytime!