Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Ashif
Contributor II
Contributor II

Need help on logic in Qlik Table

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

Labels (2)
1 Solution

Accepted Solutions
sidhiq91
Specialist II
Specialist II

@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.

View solution in original post

2 Replies
sidhiq91
Specialist II
Specialist II

@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
Contributor II
Contributor II
Author

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