Load script question

[jduluc12]

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

[sunny_talwar]

Not sure I understand the logic... why was USA not updated? It had 1 for Desc also?

[effinty2112 · Accepted solution]

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

[Anil_Babu_Samineni]

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;

[jduluc12]

Becasue we want to update only rows where Type=Ind

[sunny_talwar]

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;