Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikview979
Specialist
Specialist

Apply Map in Single line Statement

Hi Experts,

I have one mapping table like

 

FilteridRfsid
Rf01Pc01Rat111
Rf02Pc02Rat222
Rf03Pc03Rat333
Cf01ABT1
Cf02ABT2
Cf03ABT3

and  first source table like

 

FilteridPcflagName
Rf01Pc01Akash
Rf02Pc02Avinash
Rf03Pc03Srinu

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

 

FilteridName
Cf01Raj
Cf02Raja
Cf03Vivek

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

   

Filterid&PcflagApplayName
Rf01Pc01Rat111Akash
Rf02Pc02Rat222Avinash
Rf03Pc03Rat333Srinu
Cf01ABT1Raj
Cf02ABT2Raja
Cf03ABT3Vivek
1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

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;

View solution in original post

6 Replies
settu_periasamy
Master III
Master III

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;

qlikview979
Specialist
Specialist
Author

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;


Kushal_Chawda

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;

qlikview979
Specialist
Specialist
Author

Hi,

Please find the my source file.

Regards,
mahesh

qlikview979
Specialist
Specialist
Author

Hi Kushal,

Please find my attached file.

Regards,
Mahesh

settu_periasamy
Master III
Master III

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;