Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've the following data table.
Primary ID | Second ID | Data |
1 | 1A | P |
1 | 1B | Q |
1 | 1C | R |
1 | 1D | S |
2 | 2A | Q |
2 | 2B | Q |
3 | 3A | S |
3 | 3B | Q |
3 | 3C | P |
What I want to extract is Primary key where all records in data is 'Q'. So only primary key 2 should return.
I am using SQL as well to extract this, what I was able to do is a match between count(data) of total records grouped by Primary Key and count(data) where data='Q' grouped by Primary Key.
Is there a better way to do it in QlikView or in SQL maybe?
One solution will be:
tab1:
LOAD [Primary ID], If(Concat(DISTINCT Data)='Q','Y') As Flag
Group By [Primary ID]
;
LOAD * INLINE [
Primary ID, Second ID, Data
1, 1A, P
1, 1B, Q
1, 1C, R
1, 1D, S
2, 2A, Q
2, 2B, Q
3, 3A, S
3, 3B, Q
3, 3C, P
];
One solution will be:
tab1:
LOAD [Primary ID], If(Concat(DISTINCT Data)='Q','Y') As Flag
Group By [Primary ID]
;
LOAD * INLINE [
Primary ID, Second ID, Data
1, 1A, P
1, 1B, Q
1, 1C, R
1, 1D, S
2, 2A, Q
2, 2B, Q
3, 3A, S
3, 3B, Q
3, 3C, P
];