topic Re: Get the last revision value in QlikView

Get the last revision value

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I am very new to qlikview, I need help on the the following case.

I have a set of data in table "Cost"

Year	Month	Type	Company Code	Report number	Amount
2017	1	ERP	1001	001/ABCD-1	350
2017	1	EML	1001	001/ABCD-1	20
2017	1	ERP	1002	001/ABCD-1	300
2017	2	ERP	1001	002/ABCD-1	325
2017	2	EML	1001	002/ABCD-1	20
2017	2	ERP	1002	002/ABCD-1	300
2017	1	ERP	1001	002/ABCD-1	325 *
2017	3	ERP	1001	003/ABC-1	330
2017	3	EML	1001	003/ABC-1	20
2017	3	ERP	1002	003/ABC-1	300
2017	1	ERP	1001	003/ABC-1	330**
2017	2	ERP	1001	003/ABC-1	330**

* is the first revision of ERP cost for month 1 of company 1001

** is the second revision of ERP cost for month 1 and 2 of company 1001

The final report is expected something like this:

Year	Month	Type	Company Code	Report number	Amount
2017	1	EML	1001	001/ABCD-1	20
2017	1	ERP	1002	001/ABCD-1	300
2017	2	EML	1001	002/ABCD-1	20
2017	2	ERP	1002	002/ABCD-1	300
2017	3	ERP	1001	003/ABCD-1	330
2017	3	EML	1001	003/ABCD-1	20
2017	3	ERP	1002	003/ABC-1	300
2017	1	ERP	1001	003/ABC-1	330
2017	2	ERP	1001	003/ABC-1	330

The final report shows report number and amount of the latest revisions, latest cost revision may not always be lower or higher than the previous ones.

I try to use max(amount) but it will show the original cost value 350.

please help.

thank you.

Re: Get the last revision value

Or — Wed, 08 Nov 2017 08:06:37 GMT

Your data above isn't entirely consistent with the expected output below (e.g. the Month = 3 rows have the wrong report number, if I understand the data correctly). However, for the sake of trying to answer, I'm assuming that's an error and the Report number can be used to determine what is the latest revision.

At the expression level, you could try something like:

sum(Amount * If([ReportNumber] = aggr(max([Report Number]),Year,Month,Type,[Company Code]),1))

Ideally, you should flag rows that belong to the latest revision at the script level using a similar mechanism - you can group based on the key fields and use something along the lines of having(Report Number = Max(Report Number). Set this flag to 1 if true, null if not, and multiply by the flag or use it in set analysis as you prefer.

Re: Get the last revision value

Anonymous — Wed, 08 Nov 2017 08:26:36 GMT

My mistake. the final report should be like this:

Year	Month	Type	Company Code	Report number	Amount
2017	1	ERP	1001	003/ABC-1	330
2017	1	EML	1001	001/ABCD-1	20
2017	1	ERP	1002	001/ABCD-1	300
2017	2	ERP	1001	003/ABC-1	330
2017	2	EML	1001	002/ABCD-1	20
2017	2	ERP	1002	002/ABCD-1	300
2017	3	ERP	1001	003/ABC-1	330
2017	3	EML	1001	003/ABC-1	20
2017	3	ERP	1002	003/ABC-1	300

So if there's a latest update for certain period of a type the report shows the latest report number and the amount.

thank you.