Key Field for CrossTable

[#1]

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!

[#2]

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.

[#3]

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?

[#4 · Accepted solution]

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

[#5]

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!

[#6]

Anytime!