Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
QFanatic
New Contributor

Scripting assistance - Join

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.

 

Labels (1)
1 Solution

Accepted Solutions
zhadrakas
Valued Contributor II

Re: Scripting assistance - Join

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

 

 

View solution in original post

5 Replies
Highlighted
zhadrakas
Valued Contributor II

Re: Scripting assistance - Join

in frontend you can use this expression:

FirstSortedValue(distinct Amount, Incident_no)
Highlighted
QFanatic
New Contributor

Re: Scripting assistance - Join

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
Valued Contributor II

Re: Scripting assistance - Join

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

 

 

View solution in original post

Highlighted
QFanatic
New Contributor

Re: Scripting assistance - Join

Thank you very much

Highlighted
Partner
Partner

Re: Scripting assistance - Join

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