Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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 )
Highlighted
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...

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