Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Applymap to fill values

Table A:

NameID
A1
B 
C5
D 
E7

 

Table B:

NameID
A20
B2
C99
D9
E 
F8
I10
J11
K15

 

 

Result:

NameID
A20
B2
C99
D9
E7

 

 

I need to use Apply Map and populate 'ID' column using the two tables based on these conditions. 

1. If 'ID' in Table A is blank, then fill it with 'ID' from Table B

2. If 'ID' in Table A has a value, replace it with 'ID' from Table B

3. If 'ID' in Table A has a value and 'ID' in Table B is null, then use the value from table A.

I have tried

 

 if(len(ID) > 0, ID, ApplyMap('NameIdMap', Name)) as ID

 

Applymap('fieldmap',Name,ID) 			as ID1,
    if(ID>0,ID,Applymap('fieldmap',Name) ) 	as ID2;

 

But they are failing the 3rd condition I have here.

 

Please help

 

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

hi ,

here is the solution 

try the below condition in script(note:Change your map  table name)

If(Len(ApplyMap('FillmapTableB',Name))<>0,ApplyMap('FillmapTableB',Name,ID),ID) As ID

raji6763_0-1592555883403.png

 

Output:

raji6763_1-1592556498873.png

Regards,

raji

View solution in original post

3 Replies
Highlighted
Partner
Partner

have a look at this 

fieldmap:
Mapping load * inline [
Name,ID
A,20
B,2
C,99
D,9
E,
F,8
I,10
J,11
K,15
];



Table1:
load Name,
	 if(Applymap('fieldmap',Name,ID)>0,
     	 Applymap('fieldmap',Name,ID),ID)   as ID;
load * Inline [
Name,ID
A,1
B,	 
C,5
D,	 
E,7
L,10
]; 
Highlighted
Creator II
Creator II

@lironbaram  Hi, this might work. Let me try. Thank you. But what happens when the ID is alphanumeric?

 

I tried 

if(len(Applymap('fieldmap',Name,ID))>0,

 and this gives me blank values

Highlighted
Creator II
Creator II

hi ,

here is the solution 

try the below condition in script(note:Change your map  table name)

If(Len(ApplyMap('FillmapTableB',Name))<>0,ApplyMap('FillmapTableB',Name,ID),ID) As ID

raji6763_0-1592555883403.png

 

Output:

raji6763_1-1592556498873.png

Regards,

raji

View solution in original post