Creating field based on the match

[BI_Dev]

Hi - I have data as below

ID	Name
1	ABC
1
2	xyz
3

Here I have ID and Name columns, but I want to create a key column where if the ID has a Name associated I want to Concatenate ID & Name, if and ID doesn't have a name, it will be Just ID as below.

KEY

1-ABC

2-XYZ

3

I tried below but doesn't work.

Load 

ID, NAME,

IF(LEN(Name)>0, ID &'-'&Name , ID as KEY

From...

Any thoughts pls.

[MarcoWedel · Accepted solution]

IF(LEN(Name)>0, ID &'-'&Name , ID) as KEY

[BrunPierre]

Temp:
LOAD * Inline[
ID,Name
1,ABC
1,
2,xyz
3,
];

NoConcatenate
Table:
LOAD ID,
MaxString(IF(Len((Name))>0,ID &'-'&Name,Text(ID))) as KEY

Resident Temp
Group By ID;
DROP Table Temp;

DROP Field ID From Table;

EXIT SCRIPT;

[vinieme12 · Accepted solution]

As @MarcoWedel suggested, you can do this in a single load

Temp:
LOAD 
ID
,Name
,IF(LEN(Name)>0, ID &'-'&Name , ID) as KEY
 Inline[
ID,Name
1,ABC
1,
2,xyz
3,
];