Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
innashna
New Contributor

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

1 Solution

Accepted Solutions
kushalthakral
Contributor III

Re: How to fetch first row of almost same rows ?

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

4 Replies
kushalthakral
Contributor III

Re: How to fetch first row of almost same rows ?

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
New Contributor

Re: How to fetch first row of almost same rows ?

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

kushalthakral
Contributor III

Re: How to fetch first row of almost same rows ?

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
New Contributor

Re: How to fetch first row of almost same rows ?

Thanks, will try it.