load script for column

[marcvw]

Good day all, 

I try to create a script for a column named 'allotment/garantee'. We have a field called 'Key_service' whereby we have received several numbers for. These numbers should be named 'garantee'. All the rest should be named 'allotment'. 

I have the following script:

Load
KEY_Service,
if(
Match(KEY_Service, '123', '456', '789'),
'garantee',
'allotment') as [allotment/garantee]
Inline
[
KEY_Service
123
456
789
];

At this stage I can only see the values named 'garantee' however the not specified numbers appear as '- ' instead of 'allotment'

How can I resolve this?

[BrunPierre · Accepted solution]

Without remodeling, you'd need to add the condition mentioned above in every table where KEY_Service field exist throughout the model.

[marcus_sommer · Accepted solution]

Beside applying the matching in each table-load you may depending on the data-set and the requirements also remove the records of these NULL keys. This is for example a quite common approach to filter dimension-tables against the fact-tables and not loading long outdated products, staff and similar since the company start.

If this isn't suitable for your scenario you should really consider a change of the data-model because your NULL experience here means that there are missing key-values between the various tables - and this means further you will never be able to display all data together at the same time.