Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
The syntax I was talking about is Autonumber(field1&field2&field3&field4). Try that and tell me what happened.
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.
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?
The syntax I was talking about is Autonumber(field1&field2&field3&field4). Try that and tell me what happened.
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!
Anytime!