considering value of one field with respect to other field

[askarkhan]

Hi,

Please have a look over the attach file, My requirement is to have only those ids (column B) which have both normal and repeat as its TYPE (coulumn A), i tried many conditions but failed to get proper result.

Please help with this.

TYPE	ID	GROUP	YEAR	CENTER	VALUE
NORMAL	PID0001_001-4626	HOLYGRAIL_3PO	2020	Partner	98
REPEAT	PID0001_001-4626	HOLYGRAIL_3PO	2020	Partner	78.8
NORMAL	PID0001_001-4641	HOLYGRAIL_3PO	2020	Partner	89
REPEAT	PID0001_001-4641	HOLYGRAIL_3PO	2020	Partner	99
NORMAL	PID0001_001-4642	HOLYGRAIL_3PO	2020	Partner	93
REPEAT	PID0001_001-4642	HOLYGRAIL_3PO	2020	Partner	78
NORMAL	PID0001_001-4695	HOLYGRAIL_3PO	2020	Partner	77
NORMAL	PID0001_001-4695	HOLYGRAIL_3PO	2020	Partner	76
NORMAL	PID0001_001-4696	HOLYGRAIL_3PO	2020	Partner	89
NORMAL	PID0001_001-4696	HOLYGRAIL_3PO	2020	Partner	99
NORMAL	PID0001_001-4692	HOLYGRAIL_3PO	2020	Partner	56
NORMAL	PID0001_001-4693	HOLYGRAIL_3PO	2020	Partner	78
NORMAL	PID0001_001-4693	HOLYGRAIL_3PO	2020	Partner	94
NORMAL	PID0001_001-4694	HOLYGRAIL_3PO	2020	Partner	88

[Saravanan_Desingh]

Are you looking like this?

tab1:
LOAD *
Where Match(TYPE,'NORMAL','REPEAT');
LOAD RecNo() As RowID,* INLINE [
    TYPE, ID, GROUP, YEAR, CENTER, VALUE
    NORMAL, PID0001_001-4626, HOLYGRAIL_3PO, 2020, Partner, 98
    REPEAT, PID0001_001-4626, HOLYGRAIL_3PO, 2020, Partner, 78.8
    NORMAL, PID0001_001-4641, HOLYGRAIL_3PO, 2020, Partner, 89
    REPEAT, PID0001_001-4641, HOLYGRAIL_3PO, 2020, Partner, 99
    NORMAL, PID0001_001-4642, HOLYGRAIL_3PO, 2020, Partner, 93
    REPEAT, PID0001_001-4642, HOLYGRAIL_3PO, 2020, Partner, 78
    NOREPEAT, PID0001_001-4642, HOLYGRAIL_3PO, 2020, Partner, 78    
    NORMAL, PID0001_001-4695, HOLYGRAIL_3PO, 2020, Partner, 77
    NORMAL, PID0001_001-4695, HOLYGRAIL_3PO, 2020, Partner, 76
    NORMAL, PID0001_001-4696, HOLYGRAIL_3PO, 2020, Partner, 89
    NORMAL, PID0001_001-4696, HOLYGRAIL_3PO, 2020, Partner, 99
    NORMAL, PID0001_001-4692, HOLYGRAIL_3PO, 2020, Partner, 56
    NORMAL, PID0001_001-4693, HOLYGRAIL_3PO, 2020, Partner, 78
    ABNORMAL, PID0001_001-4693, HOLYGRAIL_3PO, 2020, Partner, 78    
    NORMAL, PID0001_001-4693, HOLYGRAIL_3PO, 2020, Partner, 94
    NORMAL, PID0001_001-4694, HOLYGRAIL_3PO, 2020, Partner, 88
];

RowID 7 & 14 not pulled.

[askarkhan]

No. let me explain again.

ID PID0001_001_4626 has both 'NORMAL' and 'REPEAT' as its TYPE (column A) so i want it.

whereas ID PID0001_001_4695 has only 'NORMAL' as its TYPE(column A) so i don't need that.

so my required Output should be like below. The rest should be eliminated.

    TYPE, ID, GROUP, YEAR, CENTER, VALUE
    NORMAL, PID0001_001-4626, HOLYGRAIL_3PO, 2020, Partner, 98
    REPEAT, PID0001_001-4626, HOLYGRAIL_3PO, 2020, Partner, 78.8
    NORMAL, PID0001_001-4641, HOLYGRAIL_3PO, 2020, Partner, 89
    REPEAT, PID0001_001-4641, HOLYGRAIL_3PO, 2020, Partner, 99
    NORMAL, PID0001_001-4642, HOLYGRAIL_3PO, 2020, Partner, 93
    REPEAT, PID0001_001-4642, HOLYGRAIL_3PO, 2020, Partner, 78

[Saravanan_Desingh · Accepted solution]

Check this.

tab1:
LOAD RecNo() As RowID,* INLINE [
    TYPE, ID, GROUP, YEAR, CENTER, VALUE
    NORMAL, PID0001_001-4626, HOLYGRAIL_3PO, 2020, Partner, 98
    REPEAT, PID0001_001-4626, HOLYGRAIL_3PO, 2020, Partner, 78.8
    NORMAL, PID0001_001-4641, HOLYGRAIL_3PO, 2020, Partner, 89
    REPEAT, PID0001_001-4641, HOLYGRAIL_3PO, 2020, Partner, 99
    NORMAL, PID0001_001-4642, HOLYGRAIL_3PO, 2020, Partner, 93
    REPEAT, PID0001_001-4642, HOLYGRAIL_3PO, 2020, Partner, 78
    NOREPEAT, PID0001_001-4642, HOLYGRAIL_3PO, 2020, Partner, 78    
    NORMAL, PID0001_001-4695, HOLYGRAIL_3PO, 2020, Partner, 77
    NORMAL, PID0001_001-4695, HOLYGRAIL_3PO, 2020, Partner, 76
    NORMAL, PID0001_001-4696, HOLYGRAIL_3PO, 2020, Partner, 89
    NORMAL, PID0001_001-4696, HOLYGRAIL_3PO, 2020, Partner, 99
    NORMAL, PID0001_001-4692, HOLYGRAIL_3PO, 2020, Partner, 56
    NORMAL, PID0001_001-4693, HOLYGRAIL_3PO, 2020, Partner, 78
    ABNORMAL, PID0001_001-4693, HOLYGRAIL_3PO, 2020, Partner, 78    
    NORMAL, PID0001_001-4693, HOLYGRAIL_3PO, 2020, Partner, 94
    NORMAL, PID0001_001-4694, HOLYGRAIL_3PO, 2020, Partner, 88
];
Left Join(tab1)
LOAD *, If(Index(Str,'NORMAL') And Index(Str,'REPEAT'), 'Y', 'N') As Flag;
LOAD ID, Concat(DISTINCT TYPE) As Str
Resident tab1
Group By ID
;

[Saravanan_Desingh · Accepted solution]

Output:

[askarkhan]

thanks this works