how to generate a key field with the combination of Date field and dimension

[sanjeeva_279]

Hi ,

I need to create a join between two tables, tables have two fields in common date and dimension

how to generate a key field with the combination of Date field and dimension?

[Vegar · Accepted solution]

I would say there are three ways to do it. Common for all methods is that you need to exclude the two common field from one of the tables off you want to keep them in the datamodell without having a synthetic key.

1. Creating a field by combining the two.
Date & '|' & Dimension as %KeyField

2. Auto-numbering the unique combinations.
Autonumber(Date & '|' & Dimension) %KeyField
OR
autonumberhash256(Date,Dimension) as %KeyField
OR
autonumberhash128(Date,Dimension) as %KeyField

3. Hashing the field combinations
Hash256(Date,Dimension) as %KeyField
OR
Hash128(Date,Dimension) as %KeyField

[Vegar]

A second answer/again. What do you mean by join? If you are planning to join the two tables into one inside the script using JOIN then you do not need to do anything, window will use both field in a JOIN.