Need help on logic in Qlik Table

[Ashif]

Hi Can anyone help me on this below logic due to i am new to Qlik. 

I need to show the trilogy ID's in table where only have [AE ID] and not to show where trilogy ID have both at least one [AE ID] and [PQ ID]. Please find the attached screenshot.  Please comment me if anything is required. 

Example: TU22-1018501 have both [PQ ID] and [AE ID], So it doesn't show in table.
TU22-1008335 have only AE ID, so need to show in table

[Trilogy ID]	PQ ID	AE ID
TU22-1008240		TU22-1008240-AE00
TU22-1008240		TU22-1008240-AE01
TU22-1008245		TU22-1008245-AE00
TU22-1008245	TU22-1008245-PQ00	TU22-1008245-AE01
TU22-1008335		TU22-1008335-AE00
TU22-1018501		TU22-1018501-AE00
TU22-1018501		TU22-1018501-AE01
TU22-1018501		TU22-1018501-AE02
TU22-1018501	TU22-1018501-PQ00
TU22-1018501	TU22-1018501-PQ01
TU22-1018501	TU22-1018501-PQ02
TU22-1018501	TU22-1018501-PQ03
TU22-1018501	TU22-1018501-PQ04
TU22-1018501	TU22-1018501-PQ05
TU22-1018501	TU22-1018501-PQ06
TU22-1018501	TU22-1018501-PQ07
TU22-1018501	TU22-1018501-PQ08
TU22-1018501	TU22-1018501-PQ09	TU22-1018501-AE03

Thanks in advance,

Ashif

[sidhiq91 · Accepted solution]

@Ashif  Please see the below script that I have used in the Script Editor. Also I have attached the output as per your expectation. 

NoConcatenate
Temp:
Load * inline [
Trilogy ID,PQ ID,AE ID
TU22-1008240, ,TU22-1008240-AE00
TU22-1008240, ,TU22-1008240-AE01
TU22-1008245, ,TU22-1008245-AE00
TU22-1008245, TU22-1008245-PQ00, TU22-1008245-AE01
TU22-1008335, ,TU22-1008335-AE00
TU22-1018501, ,TU22-1018501-AE00
TU22-1018501, ,TU22-1018501-AE01
TU22-1018501, ,TU22-1018501-AE02
TU22-1018501, TU22-1018501-PQ00,
TU22-1018501, TU22-1018501-PQ01,
TU22-1018501, TU22-1018501-PQ02,
TU22-1018501, TU22-1018501-PQ03,
TU22-1018501, TU22-1018501-PQ04,
TU22-1018501, TU22-1018501-PQ05,
TU22-1018501, TU22-1018501-PQ06,
TU22-1018501, TU22-1018501-PQ07,
TU22-1018501, TU22-1018501-PQ08,
TU22-1018501, TU22-1018501-PQ09, TU22-1018501-AE03
];

NoConcatenate
Temp1:
Load Count(emptyisnull([PQ ID])) as [PQ ID count],
Count(emptyisnull([AE ID])) as [AE ID Count],
[Trilogy ID]

Resident Temp
Group by [Trilogy ID];

Inner join (Temp)
Load [Trilogy ID]
Resident Temp1
where [PQ ID count]=0 and [AE ID Count]=1;

Drop table Temp1;

Exit Script;

If this resolves your issue, please like and accept it as a solution.

[Ashif]

@sidhiq91 Thank you so much for you help....!!