topic Re: Load script question in QlikView

Load script question

jduluc12 — Wed, 30 Aug 2017 13:18:56 GMT

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

Re: Load script question

sunny_talwar — Wed, 30 Aug 2017 13:34:40 GMT

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

Re: Load script question

effinty2112 — Wed, 30 Aug 2017 13:35:55 GMT

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

Re: Load script question

Anil_Babu_Samineni — Wed, 30 Aug 2017 13:38:24 GMT

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;

Re: Load script question

jduluc12 — Wed, 30 Aug 2017 13:42:12 GMT

Becasue we want to update only rows where Type=Ind

Re: Load script question

sunny_talwar — Wed, 30 Aug 2017 13:49:15 GMT

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;