topic Re: Applymap () in QlikView

Applymap ()

Anonymous — Tue, 08 Oct 2013 02:17:33 GMT

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:

from Input table;

Map_tab1:

desc1

Map_tab2:

desc1,

desc2

Map_tab3

desc2,

desc3

Expected O/P Table:

Field1	Description
a	desc1
desc1	desc2
desc2	desc3

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.

Re: Applymap ()

jagan — Tue, 08 Oct 2013 03:09:27 GMT

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.

Re: Re: Applymap ()

Anonymous — Tue, 08 Oct 2013 04:03:54 GMT

Hi Jagan,

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

StateCode	Capital	County	StareetNM
MA	Boston	Framingham	GraniteST
CA	Sacramento	Quincy	Quincy
TX	Dallas	GreenVille	AdamsStreet

Re: Re: Re: Applymap ()

Anonymous — Tue, 08 Oct 2013 04:33:34 GMT

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.

Re: Applymap ()

jagan — Tue, 08 Oct 2013 04:37:07 GMT

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 ()

Gysbert_Wassenaar — Tue, 08 Oct 2013 06:37:50 GMT

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
];

Re: Applymap ()

MayilVahanan — Tue, 08 Oct 2013 06:51:12 GMT

Try like this

Input:

LOAD * INLINE [

StateCode

];

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;

Re: Applymap ()

jonathandienst — Tue, 08 Oct 2013 07:04:47 GMT

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

[AppToDesc]:

Load

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

Re: Applymap ()

Tue, 08 Oct 2013 12:04:28 GMT

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;

Re: Applymap ()

Anonymous — Tue, 08 Oct 2013 14:13:08 GMT

Thanks to all, It is working now.

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