Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
phoenix
Creator
Creator

Load record with MAX VALUE by Account. If Account has two records with same VALUE load first record

INPUT
ACCOUNTVALUEDESC
208383108999ABC
208383108999ABC
2083833015166XYZ
208383302869XYZ
208383301979XYZ
208383301475XYZ
208383300XYZ
2083833116186ELM
208383310ELM
2083835715095GHS
2083835715095GHS
2083835715095GHS
208383575399GHS
2083838915095RTF
208383897214RTF
208383891997RTF
   

 

RESULT
ACCOUNTVALUEDESC
208383108999ABC
2083833015166XYZ
2083833116186ELM
2083835715095GHS
2083838915095RTF

 

How do I do this in the load script?

2 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I was a bit confused about you specifying "first record" as the records appear to be duplicates. If they are different in some other field please show a sample. For now, how about:

Data:
LOAD
INPUT as ACCOUNT,
F2 as VALUE,
F3 as DESC
FROM
[https://community.qlik.com/t5/New-to-QlikView/Load-record-with-MAX-VALUE-by-Account-If-Account-has-t...]
(html, utf8, embedded labels, table is @1)
Where RecNo() > 1
;

Inner Join(Data)
LOAD Distinct
max(VALUE) as VALUE,
ACCOUNT
Resident Data
Group By ACCOUNT
;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

phoenix
Creator
Creator
Author

Thanks @rwunderlich

 I have used the following code and it worked for getting the record with MAX(VALUE) and it worked

DATA:

LOAD

    ACCOUNT,

    VALUE,

    DESC

FROM ...;

right join (DATA)

load

ACCOUNT,

MAX(VALUE) as VALUE

resident DATA

group by ACCOUNT;

right join(DATA)

load

ACCOUNT,

VALUE,

FirstValue(DESC) as DESC

Resident DATA

group by ACCOUNT, VALUE;

 It is not always duplicates, these are Accounts and each account could have multiple models attached to them. I need to convert multiple rows into one row for each account. Ideally I need to get to this.

Ex: 

ACCOUNTVALUEDESC
2031880016350MTGHS26
203188000DGHS52HD
203189008086WSR42FX600E
203190005917TRENCHER
203191000BAGGER
2031910018068.51SCWSVR36A600FX
203191000SCWSCZ52V730FX
203192006465.81994106
2031930010529.44EV85222
203193000A2010ICK4CUSG
2031940013375STT61V750KA
2031950014231930875
203196003268.85933007
203197006780.99SWZ52V-20FS
2031980026265.97VTX740EKC604

 

ACCOUNTVALUEDESCCountVALUE2DESC2VALUE3DESC3VALUE4DESC4VALUE5DESC5
2031880016350MTGHS2620DGHS52HD      
203189008086WSR42FX600E25917TRENCHER      
203191000BAGGER318068.51SCWSVR36A600FX0SCWSCZ52V730FX    
203192006465.819941061        
2031930010529.44EV8522220A2010ICK4CUSG      
2031940013375STT61V750KA1        
20319500142319308751        
203196003268.859330071        
203197006780.99SWZ52V-20FS1        
2031980026265.97VTX740EKC60450VTX740EKC6040VTX740EKC6040VTX740EKC6040ECKA30

 

Model with MAX(VALUE) will be the primary MODEL and need to flag that as PRIMARY MODEL, may be separate column.

Models at account level would always be less than 10