topic Re: Applymap in QlikView

Applymap

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

Hi Experts,

I am trying to do an applymap like this .

But i am getting error of Field is not found(Pcflag)

I wrote like this

T1:

Mapping

LOAD Filterid,

Rfsid

FROM

(ooxml, embedded labels, table is Sheet1);

FOR Each vsheet in 'Sheet2','Sheet3'

T2:

LOAD *,

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

FROM

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

next vsheet;

Here i am having Sheet2 in Filterid and Pcflag . Sheet3 only Filterid is available.

please give me an idea.

Re: Applymap

avinashelite — Wed, 11 May 2016 05:24:06 GMT

try like this

T1:

Mapping

LOAD Filterid,

Rfsid

FROM

(ooxml, embedded labels, table is Sheet1);

FOR Each vsheet in 'Sheet2','Sheet3'

T2:

LOAD *,

ApplyMap('T1',Filterid,'Un-know') as app

FROM

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

next vsheet;

Re: Applymap

Kushal_Chawda — Wed, 11 May 2016 05:24:39 GMT

You are doing wrong applymap. how you are mapping id in other table? Use sheet2 and sheet3 table as mapping table and perform apply map in T1

Re: Applymap

marcus_sommer — Wed, 11 May 2016 05:24:52 GMT

You need to react on which sheet you are loading, maybe in this way:

FOR Each vsheet in 'Sheet2','Sheet3'

let vMapCriteria = if('$(vsheet)' = 'Sheet2', 'Filterid&Pcflag', 'Filterid');

T2:

LOAD *,

ApplyMap('T1',$(vMapCriteria), '#NV') as app

FROM

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

- Marcus

Re: Applymap

maniram23 — Wed, 11 May 2016 05:38:24 GMT

Hi Experts,

Here my mapping table is like this

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

and my Sheet2 and Sheet3 tabs are like this

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

and

Filterid	Name
Cf01	Raj
Cf02	Raja
Cf03	Vivek

the combination of these two tabs . i want to do an applymap in a single load load_statement.

The final output is like this

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

Re: Applymap

avinashelite — Wed, 11 May 2016 05:41:35 GMT

some thing like this

T1:

Mapping

LOAD Filterid,

Rfsid

FROM

(ooxml, embedded labels, table is Sheet1);

FOR Each vsheet in 'Sheet2','Sheet3'

T2:

LOAD *,

if($(vsheet)='Sheet2',ApplyMap('T1',Filterid&Pcflag,'Un-know'),ApplyMap('T1',Filterid,'Un-know')) as app

FROM

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

next vsheet;

Re: Applymap

marcus_sommer — Wed, 11 May 2016 06:46:37 GMT

A check if a field is available couldn't be done from inside a load-statement. It will always require a outside-logic like in my example above and storing it in a variable or creating a load-statement on the fly.

- Marcus

Re: Applymap

avinashelite — Wed, 11 May 2016 06:57:58 GMT

sorry, I didn't tested this , thanks for correcting me marcus_sommer‌