Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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 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.
@sidhiq91 Thank you so much for you help....!!