Load Company_Code, "T$CPRJ" as [Project], num("T$CPCC") as [Price Calculation Code], "T$PITM" as [Parent Item], "T$BPID" as [Subcontractor], "T$TANO" as [Task], "T$SEQN" as [Sequence No.], "T$CCUR" as [Currency], "T$SCRT" as [Subcontracting Rate], "T$CMSC" as [Calculation Method for Subcontracting Costs], "T$SRMC$1" as [Subcontracting Rate Multi Currency], "T$CPCP" as [Cost Component], "T$ITEM" as [Subassembly], "T$EFDT" as [Effective Date], "T$EXDT" as [Expiry Date], "T$CRDT" as [Creation Date]
FROM [$(vSource)\TTIUIL160.qvd](qvd);
I have to pick Data based on the [Effective date] and the condition is as below -
1) I have to pick unique data based on [Company Code],[Parent Item],[Subcontractor] and [Task].
2) I have to only pick those [Effective Date] which is the maximum for the above 4 fields and [Effective Date]<=Today().
For example, if I am getting like this I have to pick the first record
