Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have some data as below....
ID SubID ExpDate
1 ABC 8/9/16
1 CDE 8/9/16
2 FGH 7/9/16
2 IJK 8/9/16
Here I want to exclude the subspace ID's where expiry dates are different.How can I do this in the script level...
Check this:
Table:
LOAD
ID,
SubID,
SubExpire
FROM
[subtest.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE NOT([SubExpire] < today()) ;
Right Join (Table)
LOAD ID,
Max(Date([SubExpire])) as SubExpire
Resident Table
Group By ID;
So you want to exclude ID 2 here?
May be this:
Table:
LOAD * INLINE [
ID, SubID, ExpDate
1, ABC, 8/9/16
1, CDE, 8/9/16
2, FGH, 7/9/16
2, IJK, 8/9/16
];
Right Join (Table)
LOAD ID
Where Count = 1;
LOAD ID,
Count(DISTINCT ExpDate) as Count
Resident Table
Group By ID;
Sorry.I was not clear maybe.
I want to exclude SubID FGH as ID 2 has 2 SubID's with different Exp dates...but based on the ExpDate want to include the max( ExpDate.)
So output should be like below...
ID, SubID, ExpDate
1, ABC, 8/9/16
1, CDE, 8/9/16
2, IJK, 8/9/16
So you want to keep all record from the max date?
Table:
LOAD * INLINE [
ID, SubID, ExpDate
1, ABC, 8/9/16
1, CDE, 8/9/16
2, FGH, 7/9/16
2, IJK, 8/9/16
];
Right Join (Table)
LOAD Max(ExpDate) as ExpDate
Resident Table;
Or may be this:
Table:
LOAD * INLINE [
ID, SubID, ExpDate
1, ABC, 8/9/16
1, CDE, 8/9/16
2, FGH, 7/9/16
2, IJK, 8/9/16
];
Right Join (Table)
LOAD SubID,
Max(ExpDate) as ExpDate
Resident Table
Group By SubID;
My bad, you need this:
Table:
LOAD * INLINE [
ID, SubID, ExpDate
1, ABC, 8/9/16
1, CDE, 8/9/16
2, FGH, 7/9/16
2, IJK, 8/9/16
];
Right Join (Table)
LOAD ID,
Max(ExpDate) as ExpDate
Resident Table
Group By ID;
Check this:
Table:
LOAD
ID,
SubID,
SubExpire
FROM
[subtest.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE NOT([SubExpire] < today()) ;
Right Join (Table)
LOAD ID,
Max(Date([SubExpire])) as SubExpire
Resident Table
Group By ID;