I want to take only duplicate values which has one date field is available and another field should be blank

[Chinnu3]

Hi All,

I have data as 

ID	Date
12	-
13	1/2/2022
13	-
14	2/2/2022
14	3/2/2022

I want to take only duplicate values which has one date field is available and another field should be blank

Example:- ID 13 has date(1/2/2022) and another 13 has No date(blank). I don't want to consider ID 14(it has both dates).

Thanks in advance.

[abhijitnalekar]

Hi @Chinnu3 ,

Please try below create below dimension and use it in the set analysis

if(count(DateD)=1 and Count(ID)>=2,'Yes','No') 

[Chinnu3]

I want to add this one in script and i tried what you have given in above, but it didn't work. Could you please give me any other solution please.

[abhijitnalekar]

Hi @Chinnu3 ,

Can you try below in a table directly? it's working as expected at my end.

[Chinnu3]

It's working on frontend, but I need that in script and that too i want to add this in preceding load based on requirement to filter, could you please tel me how to do that..
thank you.

[brunobertels]

Hi 

Try this in script 

[Temp]:
LOAD * INLINE
[
ID,Date
12,-
13,1/2/2022
13,-
14,2/2/2022
14,3/2/2022
](delimiter is ',');

Table:
noconcatenate
Load
ID
//[Date] ,

resident Temp where Date='-';
//order by ID,Date desc ;

inner Join
Load
ID,
Date
resident [Temp] where Date<>'-' ;

noconcatenate
final:
load *resident Table where Date<>'-';

drop table Temp;
drop table Table;

resulting Table :