Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
want to create a new row called "Field ID" in the existing table and assign a foreign key to an existing field
Sample table:
Company code | Attributed fields | Count |
AA | Manager | 2 |
AA | CIO | 1 |
AA | IT Staff | 1 |
AA | Dev Staff | 1 |
AA | Management Total | 3 |
AA | Staff Total | 2 |
AB | Manager | 5 |
AB | CIO | 2 |
AB | IT Staff | 3 |
AB | Dev Staff | 2 |
AB | Management Total | 7 |
AB | Staff Total | 5 |
If attributed fields = 'Management Total " then assign 1
If attributed fields = "staff Total " assign 2.
Desired Output:
Company code | Attributed fields | Count | Field ID |
AA | Manager | 2 | 4 |
AA | CIO | 1 | 3 |
AA | IT Staff | 1 | 5 |
AA | Dev Staff | 1 | 6 |
AA | Management Total | 3 | 1 |
AA | Staff Total | 2 | 2 |
AB | Manager | 5 | 4 |
AB | CIO | 2 | 3 |
AB | IT Staff | 3 | 5 |
AB | Dev Staff | 2 | 6 |
AB | Management Total | 7 | 1 |
AB | Staff Total | 5 | 2 |
I assume you have a definition for all the keys. In my example below I use Inline[], but you can easily replace it with data from an other source if available.
Map_field:
Mapping load * Inline [
Management Total, 1
Staff Total, 2
CIO, 3
Manager, 4
];
Load *,
ApplyMap ('Map_field',[Attributed fields]) as [Field ID]
FROM [Sample Table];