Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
askarkhan
Contributor III
Contributor III

considering value of one field with respect to other field

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.

TYPEIDGROUPYEARCENTERVALUE
NORMALPID0001_001-4626HOLYGRAIL_3PO2020Partner98
REPEATPID0001_001-4626HOLYGRAIL_3PO2020Partner78.8
NORMALPID0001_001-4641HOLYGRAIL_3PO2020Partner89
REPEATPID0001_001-4641HOLYGRAIL_3PO2020Partner99
NORMALPID0001_001-4642HOLYGRAIL_3PO2020Partner93
REPEATPID0001_001-4642HOLYGRAIL_3PO2020Partner78
NORMALPID0001_001-4695HOLYGRAIL_3PO2020Partner77
NORMALPID0001_001-4695HOLYGRAIL_3PO2020Partner76
NORMALPID0001_001-4696HOLYGRAIL_3PO2020Partner89
NORMALPID0001_001-4696HOLYGRAIL_3PO2020Partner99
NORMALPID0001_001-4692HOLYGRAIL_3PO2020Partner56
NORMALPID0001_001-4693HOLYGRAIL_3PO2020Partner78
NORMALPID0001_001-4693HOLYGRAIL_3PO2020Partner94
NORMALPID0001_001-4694HOLYGRAIL_3PO2020Partner88
2 Solutions

Accepted Solutions
Saravanan_Desingh

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
;

View solution in original post

Saravanan_Desingh

5 Replies
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.

commQV71.PNG

 

askarkhan
Contributor III
Contributor III
Author

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

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
;

View solution in original post

Saravanan_Desingh

askarkhan
Contributor III
Contributor III
Author

thanks this works