topic Re: Apply Map in Single line Statement in QlikView

Apply Map in Single line Statement

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have one mapping table like

Filterid	Rfsid
Rf01Pc01	Rat111
Rf02Pc02	Rat222
Rf03Pc03	Rat333
Cf01	ABT1
Cf02	ABT2
Cf03	ABT3

and first source table like

Filterid	Pcflag	Name
Rf01	Pc01	Akash
Rf02	Pc02	Avinash
Rf03	Pc03	Srinu

and second table like .Here Pcflag field is not there in second source.

Filterid	Name
Cf01	Raj
Cf02	Raja
Cf03	Vivek

by using single load statement how can i get output like this:-

Filterid&Pcflag	Applay	Name
Rf01Pc01	Rat111	Akash
Rf02Pc02	Rat222	Avinash
Rf03Pc03	Rat333	Srinu
Cf01	ABT1	Raj
Cf02	ABT2	Raja
Cf03	ABT3	Vivek

Re: Apply Map in Single line Statement

settu_periasamy — Tue, 10 May 2016 06:30:33 GMT

Try this..

MAP:

Mapping

LOAD * INLINE [

Filterid, Rfsid

Rf01Pc01, Rat111

Rf02Pc02, Rat222

Rf03Pc03, Rat333

Cf01, ABT1

Cf02, ABT2

Cf03, ABT3

];

T1:

LOAD * INLINE [

Filterid, Pcflag, Name

Rf01, Pc01, Akash

Rf02, Pc02, Avinash

Rf03, Pc03, Srinu

];

Concatenate

LOAD * INLINE [

Filterid, Name

Cf01, Raj

Cf02, Raja

Cf03, Vivek

];

NoConcatenate

Final:

LOAD Filterid&Pcflag as Filter_Flag,Name, ApplyMap('MAP',Filterid&Pcflag,'Unknown') as Map_Field Resident T1;

DROP Table T1;

Re: Apply Map in Single line Statement

qlikview979 — Tue, 10 May 2016 06:37:03 GMT

Hi,

I want load Single load statement.

In Excel sheet1 having this data

Filterid, Pcflag, Name

Rf01, Pc01, Akash

Rf02, Pc02, Avinash

Rf03, Pc03, Srinu

and sheet2 having this data

Filterid, Name

Cf01, Raj

Cf02, Raja

Cf03, Vivek

i tried like this in my script:-

T1:

mapping

LOAD Filterid,

Rfsid

FROM

(ooxml, embedded labels, table is Sheet1);

for each vsheet in 'Sheet4','Sheet3';

T2:

LOAD

Filterid&Pcflag,

ApplyMap('T1',Filterid&Pcflag,'') as Applay,

Pcflag,

Name

FROM

(ooxml, embedded labels, table is [$(vsheet)]);

next vsheet;

Re: Apply Map in Single line Statement

Kushal_Chawda — Tue, 10 May 2016 06:38:05 GMT

T1:

LOAD * INLINE [

Filterid, Pcflag, Name

Rf01, Pc01, Akash

Rf02, Pc02, Avinash

Rf03, Pc03, Srinu

];

Concatenate

LOAD * INLINE [

Filterid, Name

Cf01, Raj

Cf02, Raja

Cf03, Vivek

];

MAP:

LOAD left(trim(Filterid),4) as Filterid,

Rfsid ;

LOAD * INLINE [

Filterid, Rfsid

Rf01Pc01, Rat111

Rf02Pc02, Rat222

Rf03Pc03, Rat333

Cf01, ABT1

Cf02, ABT2

Cf03, ABT3

];

left join (MAP)

LOAD *

Resident T1;

drop table T1;

Re: Apply Map in Single line Statement

qlikview979 — Tue, 10 May 2016 06:39:22 GMT

Hi,

Please find the my source file.

Regards,
mahesh

Re: Apply Map in Single line Statement

qlikview979 — Tue, 10 May 2016 06:40:44 GMT

Hi Kushal,

Please find my attached file.

Regards,
Mahesh

Re: Apply Map in Single line Statement

settu_periasamy — Tue, 10 May 2016 07:47:08 GMT

Hi,

Pcflag is not available in the 4 the sheet..

May be try this script..

SET ERRORMODE=0;

DIRECTORY ;

T1:

mapping

LOAD Filterid,

Rfsid

FROM

[Applay Map.xlsx]

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD * Inline [

Temp

];

for each vsheet in 'Sheet4','Sheet3';

temp_1:

LOAD * FROM [Applay Map.xlsx]

(ooxml, embedded labels, table is [$(vsheet)]);

LET x = FieldNumber('Pcflag','temp_1');

if $(x) = 0 then

Concatenate(temp_1)

LOAD * Inline [

Pcflag

];

end if

Concatenate(Temp)

LOAD

Filterid&Pcflag,

ApplyMap('T1',Filterid&Pcflag,'') as Applay,

Pcflag,

Name

Resident temp_1;

DROP Table temp_1;

next vsheet;

DROP Fields Temp;