Discussion board where members can get started with QlikView.
Please find attached my very simple model - its not my real model, just a simplified version to get my problem across.
The Two tables joins on Incident_no.
What I want to do - if there is more than one record, per Incident_no, then I only want to return the individual amount, not the sum of them....for example...
Incident 1, after join, returns 30..i only want '10'...
I can amend the Expression, like on my chart, to divide by Count(Incident_rk). That works fine if Incident_rk is a Dimension in the chart. BUT that is not always the case.
how can I, in the code, or front end - doesn't matter - get the same effect?
My reason for this issue is that in a model, I need to join 2 tables with different levels of granularity, and this would be the quickest way.
sure that sounds good.
this should do it
FirstSortedValue(Distinct Amount, Incident_no) as Amount
Group by Incident_no
LOAD * INLINE [
View solution in original post
in frontend you can use this expression:
FirstSortedValue(distinct Amount, Incident_no)
Thank you for your reply
How would I do this in script? I think doing it there might be the best, if I need to repeat the value on the front end
Thank you very much
You can load only distinct values from the table that contains duplicates, i.e.,:
Incident_info:LOAD DISTINCT* INLINE [Incident_no, Amount1, 101, 101, 102, 203, 303, 30];