topic Re: Apply Map Function in QlikView

Apply Map Function

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

Maptab: Mapping LOAD * INLINE [

ID, NAME

1, A

3, C

4, D

6, E ];

This is my mapping table

using apply map i want replace null value with some names and NAME Field get into the main table

MainTable:
LOAD [Customer ID],
Details,
Product,

ApplyMap('Maptab',[Customer ID],'notnull') as Name
FROM
C:\Users\new\Documents\Applymap.xlsx
(ooxml, embedded labels, table is Sheet1);

this is my main table

please help me

thanks & regards

hareesh

Re: Apply Map Function

Vegar — Tue, 26 Mar 2019 09:58:13 GMT

 Maptab:
 Mapping LOAD 
 	ID, 
 	IF(len(NAME)=0,null(),NAME) 
 INLINE [
    ID, NAME
    1, A
    2, 
    3, C
    4, D
    5, 
    6, E
    7, F
    8, G
    9, H
];

Applymap:
LOAD [Customer ID], 
     Details, 
     Product,
     
     ApplyMap('Maptab',[Customer ID],'notnull')  as Name
FROM
Applymap.xlsx
(ooxml, embedded labels, table is Sheet1);

I added an Nullvalue in your xlsx-file

Re: Apply Map Function

Hareeshkommineni — Tue, 26 Mar 2019 10:20:04 GMT

but in Name fields It display blanks only

balnks are not filled

Re: Apply Map Function

Vegar — Tue, 26 Mar 2019 10:26:39 GMT

I might have misunderstod you. In my example it returns null for the empty names, but if you want another value just change Null() to 'notnull'

Maptab:
 Mapping LOAD 
 	ID, 
 	IF(len(NAME)=0,'NotNull',NAME) 
 INLINE [
    ID, NAME
    1, A
    2, 
    3, C
    4, D
    5, 
    6, E
    7, F
    8, G
    9, H
];

Another approach could be exclued the empty rows in your map table

 Maptab:
 Mapping LOAD * INLINE [
    ID, NAME
    1, A
    2, 
    3, C
    4, D
    5, 
    6, E
    7, F
    8, G
    9, H
]
WHERE
   Len(NAME) > 0
;

Re: Apply Map Function

Vegar — Tue, 26 Mar 2019 10:28:04 GMT

Re: Apply Map Function

Hareeshkommineni — Tue, 26 Mar 2019 10:31:52 GMT

yeah its working thank you for your help