Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
;
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.
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
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
;
Output:
thanks this works