topic Re: max month based on posting month in QlikView

max month based on posting month

venkatbza — Sat, 16 Nov 2024 04:59:44 GMT

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

Re: max month based on posting month

Somasundaram — Tue, 11 Dec 2018 14:05:05 GMT

Hi Venkatbza,

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

Re: max month based on posting month

venkatbza — Tue, 11 Dec 2018 14:21:49 GMT

Hi Somasundaram,

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

Thanks,

Venkat

Re: max month based on posting month

CarlosAMonroy — Tue, 11 Dec 2018 23:21:03 GMT

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