Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
DipeshVadgama
Partner - Creator II
Partner - Creator II

Mapping Table & Removing duplicates Rows in Load

Hi,

I have

Database_Table:

EMP ID    Defect Code

123    C856

124    

125    

126    C956

127    

128    

131    C653

132    

133    

134    X555

136    

137    S555

138    

139    

Mapping_Table:

Emp ID    Defect Code

126    C653

128    S555

132    R111

134    T123

139    Y159

After Apply Map ( Applymap('Database_Table',[Defect Code]) As New_Code)

But i am getting duplicate values.

I would like to remove Duplicate 'Imp Id' which either have twice New Code or blank Code and get result as

   

EMP ID

New_Code

Remove Row
123C856
124
125
126C956Remove
126C653
127
128 Remove
128S555
131C653
132 Remove
132R111
133
134X555Remove
134T123
136
137S555
138
139 Remove
139Y159
1 Solution

Accepted Solutions
sunny_talwar

I don't see any duplication with mapping load

MappingTable:

Mapping

LOAD * Inline [

Emp ID,    Defect Code

126,    C653

128,    S555

132,    R111

134,    T123

139,    Y159

];

Table:

LOAD *,

ApplyMap('MappingTable', [EMP ID], [Defect Code]) as New_Code;

LOAD * Inline [

EMP ID,    Defect Code

123,    C856

124,   

125,   

126,    C956

127,   

128,   

131,    C653

132,   

133,   

134,    X555

136,   

137,    S555

138,   

139,

];

Output on front end

Capture.PNG

View solution in original post

9 Replies
sunny_talwar

I don't see any duplication with mapping load

MappingTable:

Mapping

LOAD * Inline [

Emp ID,    Defect Code

126,    C653

128,    S555

132,    R111

134,    T123

139,    Y159

];

Table:

LOAD *,

ApplyMap('MappingTable', [EMP ID], [Defect Code]) as New_Code;

LOAD * Inline [

EMP ID,    Defect Code

123,    C856

124,   

125,   

126,    C956

127,   

128,   

131,    C653

132,   

133,   

134,    X555

136,   

137,    S555

138,   

139,

];

Output on front end

Capture.PNG

effinty2112
Master
Master

Hi Dipesh,

Try

Applymap('Mapping_Table',[Defect Code]) As New_Code


the first argument of applymap should be the name of the mapping table.


Good luck


Andrew

DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

Thanks, You both are right. I must be doing something wrong

DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

how do i remove Duplicate Id it is in both table

sunny_talwar

I don't see any duplicates, but in case there were duplicated, which one would you want to keep?

DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

If it is in Database_Table:

Like

ID,     Field 1, Field 2, .......

123

124

121

123

125

124

sunny_talwar

Which of the two ID 123 would you want to keep?

DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

I would like to remove Duplicate New_Code, Like

ID              New_code

123

123            222

243            123

456           

123            1111

Result

ID              New_code

123            222 or 1111

243          123

456            

sunny_talwar

May be just do like this

LOAD ID

FROM ....

Where ID <> Previous(ID);