Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Re: Max Date

Hi Gerhard,
Well first of all, if you really want to fetch the last [decision result] for each application, you can do something like:
sum(
if(
if (aggr( NODISTINCT max(date), application) = date, [decision result] ) = 'Approved', 1
))
That will fetch the last decision result for any application.
Next problem is you want to use the [decision result] dimension. The problem is if you use that, the records selected for your calculations will be limited to your selection, so count( distinct application ) no longer gives you a count of all applications. You can ignore the selected dimension in your calculation with a set modifier <> as part of a set analysis {} :
count( {$<[decision result]=>} distinct application )
gerhardl
Contributor II

Re: Max Date

Hi Jeroen,

I'm really sorry, I am still figuring all of this stuff out, and I do not understand what you mean.

Where should I use the sum expression?

What I REALLY want to do is change my load script to only import unique application number, and only the ones with the latest decision reslut.

That way I will not have to use any of these distinct expressions, I can just go on using my normal expression that I already have in place.

So load the same [Appl No] only once - always the one from the latest application extract or with the latest decision result.

I'm so confused...

Highlighted
Not applicable

Re: Max Date

Well that's the problem with getting your expressions accurate, they get harder to understand. That's why I prefer simple solutions like the first ones I posted.

The first if() returns the value 1is the [decision result] equals approved. You can just Sum() these 1's.

If you want to change your load script you could do something like:

LAST_APPLICATION:

LOAD application as LA_application, Max([decision date]) as LA_decision_date

RESIDENT APPLICATION

GROUP BY application;

INNER JOIN

LOAD application as LA_application, [decision date] as LA_decision_date, ... other fields

RESIDENT APPLICATION;

gerhardl
Contributor II

Re: Max Date

Wow - I really am starting to think I am too dumb for qlikview. Granted, I have never studied IT, I've never done any coding or SQL in my life, so this is all greek to me - I have to figure it out as I go.

I know I've already asked too much of you - but I cannot seem to get it right. I have attached a word file with my load script. And I have highlighted the fields that are relevan to this.

If you could just edit the script so only the applications with the latest decision date is loaded, I would very very much appreciate it. I'm afraid I do not understand where in my script to type the above - I keep getting errors.

Thank you so much for your patience.

Regards,

G

Community Browser