Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Much appreciated.
sure that sounds good.
this should do it
Incident_info:
Load Incident_no,
FirstSortedValue(Distinct Amount, Incident_no) as Amount
Group by Incident_no
;
LOAD * INLINE [
Incident_no, Amount
1, 10
1, 10
1, 10
2, 20
3, 30
3, 30
];
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
sure that sounds good.
this should do it
Incident_info:
Load Incident_no,
FirstSortedValue(Distinct Amount, Incident_no) as Amount
Group by Incident_no
;
LOAD * INLINE [
Incident_no, Amount
1, 10
1, 10
1, 10
2, 20
3, 30
3, 30
];
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, Amount
1, 10
1, 10
1, 10
2, 20
3, 30
3, 30
];