topic Re: Mapping Table & Removing duplicates Rows in Load in QlikView

Mapping Table & Removing duplicates Rows in Load

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

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
123	C856
124
125
126	C956	Remove
126	C653
127
128		Remove
128	S555
131	C653
132		Remove
132	R111
133
134	X555	Remove
134	T123
136
137	S555
138
139		Remove
139	Y159

Re: Mapping Table & Removing duplicates Rows in Load

sunny_talwar — Fri, 28 Jul 2017 15:46:19 GMT

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

Re: Mapping Table & Removing duplicates Rows in Load

effinty2112 — Fri, 28 Jul 2017 15:46:47 GMT

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

Re: Mapping Table & Removing duplicates Rows in Load

DipeshVadgama — Fri, 28 Jul 2017 16:02:59 GMT

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

Re: Mapping Table & Removing duplicates Rows in Load

DipeshVadgama — Fri, 28 Jul 2017 16:11:28 GMT

how do i remove Duplicate Id it is in both table

Re: Mapping Table & Removing duplicates Rows in Load

sunny_talwar — Fri, 28 Jul 2017 16:14:26 GMT

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

Re: Mapping Table & Removing duplicates Rows in Load

DipeshVadgama — Fri, 28 Jul 2017 16:16:36 GMT

If it is in Database_Table:

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

123

124

121

123

125

124

Re: Mapping Table & Removing duplicates Rows in Load

sunny_talwar — Fri, 28 Jul 2017 16:21:00 GMT

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

Re: Mapping Table & Removing duplicates Rows in Load

DipeshVadgama — Fri, 11 Aug 2017 13:59:39 GMT

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

Re: Mapping Table & Removing duplicates Rows in Load

sunny_talwar — Fri, 11 Aug 2017 14:58:57 GMT

May be just do like this

LOAD ID

FROM ....

Where ID <> Previous(ID);