Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
venkatbza
Creator
Creator

max month based on posting month

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

3 Replies
Somasundaram
Creator III
Creator III

Hi Venkatbza,

 

Using AGGR() function we can able to get the correct value.

 


-Somasundaram

If this resolves your Query please like and accept this as an answer.
venkatbza
Creator
Creator
Author

Hi Somasundaram,

 

Thanks for your reply, it is good if you post expression here.

 

Thanks,

Venkat

CarlosAMonroy
Creator III
Creator III

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