Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
innashna
Contributor II
Contributor II

How to fetch first row of almost same rows ?

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

Labels (3)
1 Solution

Accepted Solutions
kushalthakral
Creator III
Creator III

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

View solution in original post

4 Replies
kushalthakral
Creator III
Creator III

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

 

innashna
Contributor II
Contributor II
Author

Hi Kushal,
Thank you.
I would be very grateful if you could write me piece of code including applymap function use.

kushalthakral
Creator III
Creator III

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

innashna
Contributor II
Contributor II
Author

Thanks, will try it.