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]

Your provided script will not assign 'allotment' or '-' because the condition is true for all values.

You can do this to verify the non specified numbers in your actual source table.

LOAD KEY_Service
FROM <Source>
Where Match(KEY_Service, '123', '456', '789') = 0;

[Anil_Babu_Samineni]

@marcvw -the script works as it should, I can see the input data you have only with 3 values, If you add one more what is coming? any screenshot?

[marcvw]

Thank you for your response in here. Would it be possible to assign 'allotment' to this column next to garantee? How can I enable this. Appreciate your support in here. 

[marcvw]

Hi Anil, I see the column 'allotment/garantee' and only values 'garantee' for the values provided and '-' for the non-provided values. Instead of '-' I would like to see the 'allotment' value. Can this be accomplished? 

[BrunPierre]

"-" typically indicates that the field has NULL or empty values.

You can adjust your script to handle such values appropriately or replace "-" with a different representation as needed, as in your case "allotment".

If(Match(KEY_Service, '123', '456', '789'), 'garantee',
If(Len(Trim(KEY_Service))= 0, 'allotment', 'allotment')) as [allotment/garantee]

[marcvw]

I've modified the script, see enclosed result. Are there any other ways to reesolve this? Thanks in advance. 

[marcus_sommer]

Your condition will always return a value of TRUE or FALSE and not NULL unless there is anything which might break the expression which seems not likely in regard to your example but may theoretically happens if there are in reality variables in use and/or further loops or other functions.

This means your NULL will probably come from a different table which also contained the KEY_Service as field.

[BrunPierre]

Based on @marcus_sommer 's explanation, share the data model.

[marcvw]

Data model is attached. Thank you.