Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table with the following set of data.
Type Num Desc Name
Eng 1 MVR DEF
Ind 2 1
Eng 3 TRY ERT
USA 1 1
I need to write a script to update the Name field of Type Ind to the value of Name field of Type Eng where Num of Type Eng is equal to Desc of Type Ind.
so the output should be
Type Num Desc Name
Eng 1 MVR DEF
Ind 2 1 DEF
Eng 3 TRY ERT
USA 1 1
Thanks in advance,
Jean
Hi Jean,
Try:
Data:
LOAD * Inline [
Type, Num, Desc, Name
Eng, 1, MVR, DEF
Ind, 2, 1,
Eng, 3, TRY, ERT
USA, 1, 1,
];
MappingName:
Mapping
Load
Num,
Name
Resident Data Where Type = 'Eng';
NoConcatenate
Table:
LOAD
Type, Num, Desc,
if(Type = 'Ind',ApplyMap('MappingName',Desc,'No Eng Name'),Name) as Name
Resident Data;
DROP table Data;
to get:
Type | Num | Desc | Name |
---|---|---|---|
Eng | 1 | MVR | DEF |
Eng | 3 | TRY | ERT |
Ind | 2 | 1 | DEF |
USA | 1 | 1 |
Cheers
Andrew
Not sure I understand the logic... why was USA not updated? It had 1 for Desc also?
Hi Jean,
Try:
Data:
LOAD * Inline [
Type, Num, Desc, Name
Eng, 1, MVR, DEF
Ind, 2, 1,
Eng, 3, TRY, ERT
USA, 1, 1,
];
MappingName:
Mapping
Load
Num,
Name
Resident Data Where Type = 'Eng';
NoConcatenate
Table:
LOAD
Type, Num, Desc,
if(Type = 'Ind',ApplyMap('MappingName',Desc,'No Eng Name'),Name) as Name
Resident Data;
DROP table Data;
to get:
Type | Num | Desc | Name |
---|---|---|---|
Eng | 1 | MVR | DEF |
Eng | 3 | TRY | ERT |
Ind | 2 | 1 | DEF |
USA | 1 | 1 |
Cheers
Andrew
Try like below?
Directory:
Load * From Table;
Mapping Load * Resident Where Type = 'Eng';
For Mapping table you required this
Load *, if(Type = 'Ind',ApplyMap('MappingName',Desc,'xxx'),Name) as Name Resident Directory;
Becasue we want to update only rows where Type=Ind
Another option
Table:
LOAD * INLINE [
Type, Num, Desc, Name
Eng, 1, MVR, DEF
Ind, 2, 1
Eng, 3, TRY, ERT
USA, 1, 1
];
Left Join (Table)
LOAD Num as Desc,
Name as NewName
Resident Table
Where Len(Trim(Name)) > 0;
FinalTable:
LOAD Type,
Num,
Desc,
If(Len(Trim(Name)) = 0 and Type = 'Ind', NewName, Name) as Name
Resident Table;
DROP Table Table;