Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Partner
Partner

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
Highlighted

Re: Mapping Table & Removing duplicates Rows in Load

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
Highlighted

Re: Mapping Table & Removing duplicates Rows in Load

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

Highlighted
Honored Contributor

Re: Mapping Table & Removing duplicates Rows in Load

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

Highlighted
Partner
Partner

Re: Mapping Table & Removing duplicates Rows in Load

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

Highlighted
Partner
Partner

Re: Mapping Table & Removing duplicates Rows in Load

how do i remove Duplicate Id it is in both table

Highlighted

Re: Mapping Table & Removing duplicates Rows in Load

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

Highlighted
Partner
Partner

Re: Mapping Table & Removing duplicates Rows in Load

If it is in Database_Table:

Like

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

123

124

121

123

125

124

Highlighted

Re: Mapping Table & Removing duplicates Rows in Load

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

Highlighted
Partner
Partner

Re: Mapping Table & Removing duplicates Rows in Load

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            

Highlighted

Re: Mapping Table & Removing duplicates Rows in Load

May be just do like this

LOAD ID

FROM ....

Where ID <> Previous(ID);