Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi friends,
please help me for the below requirement.
I have a table and it has a columns like ID,ID1,posting month and month. each Id has multiple ID1's and each ID1 have single posting Month and each posting month has multiple months.
in this case i need to get max month based on the ID1 and posting period but month values should be less then or equal to posting month it should not pic greater then posting period.
ex:
As per the below table if i select Id1 as 6666 then i will get only one posting month that is '04' and i will get four records in months that is 01,02,04,05.
But I need to get only 04 month record if available, or in case if 04 month is not available then it should take lass month value 02. remaining need to exclude in output.
I have a data like this
ID | ID1 | Posting Month | Month |
1001 | 6666 | 04 | 01 |
1001 | 7777 | 04 | 01 |
1001 | 8888 | 06 | 01 |
1001 | 9999 | 06 | 01 |
1001 | 6666 | 04 | 02 |
1001 | 7777 | 04 | 02 |
1001 | 8888 | 06 | 02 |
1001 | 9999 | 06 | 02 |
1001 | 6666 | 04 | 04 |
1001 | 7777 | 04 | 04 |
1001 | 8888 | 06 | 04 |
1001 | 9999 | 06 | 04 |
1001 | 6666 | 04 | 05 |
1001 | 7777 | 04 | 05 |
1001 | 8888 | 06 | 05 |
1001 | 9999 | 06 | 05 |
Hi Venkatbza,
Using AGGR() function we can able to get the correct value.
Hi Somasundaram,
Thanks for your reply, it is good if you post expression here.
Thanks,
Venkat
Hi,
You can achieve this by grouping first just the max Month values by ID1, then join the result to the Original table, that way you will now be able to compare and pull only data where PostingMonth is less or equal to MaxMonth(New Field Created.)
In QV words would be something like:
TmpTable:
Load ID1, max(Month) as MaxMonth From Table Group by ID1;
Table:
Load ID, ID6, PostingMonth, Month From Table;
Left Join (Table)
Load * Resident TmpTable;
Drop table TmpTable;
NoConcatenate
TableFinal:
Load * Resident Table Where PostingMonth <= MaxMonth;
Drop Table Table;
Hope that helps,
Carlos M