topic Scripting assistance - Join in QlikView

Scripting assistance - Join

QFanatic — Sat, 16 Nov 2024 01:35:31 GMT

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.

Re: Scripting assistance - Join

zhadrakas — Mon, 06 Jan 2020 06:42:19 GMT

in frontend you can use this expression:

FirstSortedValue(distinct Amount, Incident_no)

Re: Scripting assistance - Join

QFanatic — Mon, 06 Jan 2020 07:58:07 GMT

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

Re: Scripting assistance - Join

zhadrakas — Mon, 06 Jan 2020 08:07:46 GMT

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

Re: Scripting assistance - Join

QFanatic — Mon, 06 Jan 2020 08:22:03 GMT

Thank you very much

Re: Scripting assistance - Join

aivislau — Mon, 06 Jan 2020 12:18:22 GMT

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