Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jaswantc
Contributor II

Applymap ()


Hi Friends,

I am to Forum and seeking help of the below scenario.

I would like to build table by using below 3 mapping tables.

Inputs:

=======

Input table:

a,

b,

c

from Input table;

Map_tab1:

a,

desc1

Map_tab2:

desc1,

desc2

Map_tab3

desc2,

desc3

Expected O/P Table:

Field1Description
adesc1
desc1desc2
desc2desc3

I used below script but sofar not succieded.

 

[AppToDesc]:

Load
a,
ApplyMap('Map_tab1',a,a as desc1,
ApplyMap('Map_tab2',Peek('desc1',,'AppToDesc1'),desc1) as desc2,

ApplyMap('Map_tab3',Peek('desc2',,'AppToDesc2'),desc2) as desc3,

Resident [Input table];
Here i am getting error message says that field 'desc1' not found.

Thanks,

JaswantC.

Tags (1)
1 Solution

Accepted Solutions

Re: Applymap ()

Hi

Try like this

Input:

LOAD * INLINE [

    StateCode

    MA

    CA

    TX

    NH

];

Map_tab1:

Mapping LOAD * INLINE [

    StateCode, Capital

     MA, Boston

     CA, Sacramento

     TX, Dallas

];

Map_tab2:

Mapping LOAD * INLINE [

    Capital, County

    Boston, Framingham

    Sacramento, Quincy

    Dallas, Greenville

   ];

Map_tab3:

Mapping LOAD * INLINE [

    County, StreetNM

    Framingham, GraniteSt

    Greenville, Admasstreet

];

FieldToStreet:

LOAD *, ApplyMap('Map_tab3',County,County) as [StreetNM];

LOAD *, ApplyMap('Map_tab2',Capital,Capital) as [County];

Load

StateCode as StateCode,

ApplyMap('Map_tab1',StateCode,StateCode) as [Capital]

Resident Input;

9 Replies
MVP
MVP

Re: Applymap ()

Hi Jaswant,

Can you attach some sample data/ file, it helps in understanding.  Seems that your script is wrong can you post entire script.

Regards,

Jagan.

jaswantc
Contributor II

Re: Re: Applymap ()

Hi Jagan,

Please find attached app and i am looking below result from the app.

StateCodeCapitalCountyStareetNM
MABostonFraminghamGraniteST
CASacramentoQuincyQuincy
TXDallasGreenVilleAdamsStreet
jaswantc
Contributor II

Re: Re: Re: Applymap ()


Hi Jagan,

Some how i am close to solution but i would like to get rid of duplicate records if i use distinct again i am not getting desired results.

MVP
MVP

Re: Applymap ()

Hi,

Please find the script below

 


Input:

LOAD * INLINE [
StateCode
MA
CA
TX
NH
]
;



Map_tab1:
Mapping LOAD * INLINE [
StateCode, Capital
MA, Boston
CA, Sacramento
TX, Dallas
]
;

Map_tab2:
Mapping LOAD * INLINE [
Capital, County
Boston, Framingham
Sacramento, Quincy
Dallas, Greenville
]
;

Map_tab3:
Mapping LOAD * INLINE [
County, StreetNM
Framingham, GraniteSt
Greenville, Admasstreet
]
;

FieldToStreet:
LOAD
*,
ApplyMap('Map_tab2',Peek('County'),County) as [StreetNM];
LOAD
*,
ApplyMap('Map_tab2', Capital,'N/A') as [County];
Load
StateCode as StateCode,
ApplyMap('Map_tab1',StateCode,'N/A') as [Capital]
Resident Input;


DROP TABLE Input;

Regadrs,

Jagan.


Re: Applymap ()

Try

Map_tab1:

Mapping LOAD * INLINE [

    StateCode, Capital

     MA, Boston

     CA, Sacramento

     TX, Dallas

];

Map_tab2:

Mapping LOAD * INLINE [

    Capital, County

    Boston, Framingham

    Sacramento, Quincy

    Dallas, Greenville

   ];

Map_tab3:

Mapping LOAD * INLINE [

    County, StreetNM

    Framingham, GraniteSt

    Greenville, Admasstreet

];

Input:

LOAD

    StateCode

    , ApplyMap('Map_tab1',StateCode) as Capital

    , ApplyMap('Map_tab2',ApplyMap('Map_tab1',StateCode)) as County

    , ApplyMap('Map_tab3',ApplyMap('Map_tab2',ApplyMap('Map_tab1',StateCode))) as StreetNM  

INLINE [

    StateCode

    MA

    CA

    TX

    NH

];


talk is cheap, supply exceeds demand

Re: Applymap ()

Hi

Try like this

Input:

LOAD * INLINE [

    StateCode

    MA

    CA

    TX

    NH

];

Map_tab1:

Mapping LOAD * INLINE [

    StateCode, Capital

     MA, Boston

     CA, Sacramento

     TX, Dallas

];

Map_tab2:

Mapping LOAD * INLINE [

    Capital, County

    Boston, Framingham

    Sacramento, Quincy

    Dallas, Greenville

   ];

Map_tab3:

Mapping LOAD * INLINE [

    County, StreetNM

    Framingham, GraniteSt

    Greenville, Admasstreet

];

FieldToStreet:

LOAD *, ApplyMap('Map_tab3',County,County) as [StreetNM];

LOAD *, ApplyMap('Map_tab2',Capital,Capital) as [County];

Load

StateCode as StateCode,

ApplyMap('Map_tab1',StateCode,StateCode) as [Capital]

Resident Input;

MVP
MVP

Re: Applymap ()

Hi

I am not sure of your requirements, but to use nested applymaps, then do something like:

[AppToDesc]:

Load

  a,

  ApplyMap('Map_tab1', a) as desc1,

  ApplyMap('Map_tab2', ApplyMap('Map_tab1', a)) as desc2,

  ApplyMap('Map_tab3', ApplyMap('Map_tab2', ApplyMap('Map_tab1', a))) as desc3

Resident [Input table];

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Applymap ()

  Use this code and run. Tried with Jonathan approach. It works fine if we create with nested applymap.

Input:

LOAD * INLINE [
StateCode
MA
CA
TX
NH
]
;



Map_tab1:
Mapping LOAD * INLINE [
StateCode, Capital
MA, Boston
CA, Sacramento
TX, Dallas
]
;

Map_tab2:
Mapping LOAD * INLINE [
Capital, County
Boston, Framingham
Sacramento, Quincy
Dallas, Greenville
]
;

Map_tab3:
mapping LOAD * INLINE [
County, StreetNM
Framingham, GraniteSt
Greenville, Admasstreet
]
;

FieldToStreet:
Load
StateCode as StateCode,
ApplyMap('Map_tab1',StateCode) as [Capital],
ApplyMap('Map_tab2',ApplyMap('Map_tab1',StateCode)) as [County],
ApplyMap('Map_tab3',ApplyMap('Map_tab2',ApplyMap('Map_tab1',StateCode))) as [StreetNM]
Resident Input;

DROP Table Input;

jaswantc
Contributor II

Re: Applymap ()


Thanks to all, It is working now.

Whats the wrong with my app previously why peek function did not work.

Community Browser