Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
venkatbza
New Contributor III

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
Contributor III

Re: max month based on posting month

Hi Venkatbza,

 

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

 


-Somasundaram
venkatbza
New Contributor III

Re: max month based on posting month

Hi Somasundaram,

 

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

 

Thanks,

Venkat

CarlosAMonroy
Contributor III

Re: max month based on posting month

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