Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have qvd with rows contained of 4 fields : A,B,C,D.
In case when there is more than one row where A, B,C the same but D is different, I need to take only one random row.
Example :
row 1 :
a b c d1
row2:
a b c d2
The expected result :
a b c d1
Please advise.
Inna
Hi
Please find the script below
Table1:
Load * Inline [
EmpId, EmpName, Country, Sal
1, ABC, UK, 12
1, ABC, UK, 13
]
;
Mapping_Table:
Mapping LOAD
EmpId&'-'&EmpName&'-'&Country AS %Key,
Sal AS %Val
Resident Table1;
NoConcatenate
TableFinal:
Load
DISTINCT
EmpId,
EmpName,
Country,
ApplyMap('Mapping_Table',EmpId&'-'&EmpName&'-'&Country,'NA') AS Sal
Resident Table1;
Drop Table Table1;
Let me know in case it is not your requirement
Thanks
Kushal
Hi
You can use Applymap function in this case supposse you have table like below
EmpId, EmpName, Country, Sal
1, ABC, UK, 12
1, ABC, UK, 13
You can use mapping table like
MAP LOAD
EmpId&'-'EmpName&'-'&Country AS %Key
Sal AS %Val
Resident Table 1;
and then use applymap function in the main table which will only take first occurrence of Sal for common fields
Thanks
Kushal
Hi
Please find the script below
Table1:
Load * Inline [
EmpId, EmpName, Country, Sal
1, ABC, UK, 12
1, ABC, UK, 13
]
;
Mapping_Table:
Mapping LOAD
EmpId&'-'&EmpName&'-'&Country AS %Key,
Sal AS %Val
Resident Table1;
NoConcatenate
TableFinal:
Load
DISTINCT
EmpId,
EmpName,
Country,
ApplyMap('Mapping_Table',EmpId&'-'&EmpName&'-'&Country,'NA') AS Sal
Resident Table1;
Drop Table Table1;
Let me know in case it is not your requirement
Thanks
Kushal