Scripting assistance - Join

[QFanatic]

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.

[zhadrakas]

in frontend you can use this expression:

FirstSortedValue(distinct Amount, Incident_no)

[QFanatic]

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

[zhadrakas · Accepted solution]

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 
];

[QFanatic]

Thank you very much

[aivislau]

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
];