Give one description based on hierarchy

[Anonymous]

I have an app that uses hospital claims data, where each claim could have several codes and each code has a certain description (Lab, Surgery, etc.). I only want to bring in one description for the entire claim regardless of how many different descriptions are associated with that claim. To decide what description to use, I need to use the following hierarchy:

If any of the codes on the claim are considered "Surgery", make the whole claim description "Surgery".

Otherwise, if any if any of the codes on the claim are considered "Lab", make the whole claim description "Lab".

Otherwise, make the whole claim description "Other".

Here's a simplified version of the data:

Claim #	Code	Desc	What I want Desc to Be
1	12345	Surgery	Surgery
1	56789	Lab	Surgery
1	11111	Other	Surgery
2	98765	Lab	Lab
2	11111	Other	Lab

So for claim # 1, I want a column where all 3 lines would be "Surgery".

For claim # 2, I want a column where both lines would be "Lab".

Any ideas?
Thanks

[#2]

Try Like Below:

TEMP:

LOAD * INLINE [

Claim# , Code , Desc

1, 12345, Surgery

1, 56789, Lab

1, 11111, Other

2, 98765, Lab

2, 11111, Other

];

FIANL:

LOAD *, IF( Previous(Claim#) <> Claim# , Desc, Peek(NewDesc) ) AS NewDesc

Resident TEMP order by Claim#;

DROP Table TEMP ;

[#3]

if you data already order by Cliam# you can directly use if condition in your load script like below:

Load Claim#,

        Code,

        Desc,

        IF( Previous(Claim#) <> Claim# , Desc, Peek(NewDesc) ) AS NewDesc

From Source ;

[Anonymous]

The codes aren't in any particular order on the claim. So for example the Surgery code could be the last code on the claim but if it's there then all the other codes on that claim need a description of Surgery.

[#5 · Accepted solution]

Try Like below:

TEMP:

LOAD * INLINE [

Claim# , Code , Desc

1, 12345, Surgery

2, 11111, Other

1, 56789, Lab

1, 11111, Other

2, 98765, Lab

];

Left Join

LOAD Claim# , Upper(Concat(DISTINCT Desc ,'|' )) AS TempDesc

Resident TEMP Group By Claim# ;

FINAL:

NoConcatenate

LOAD Claim# ,

       Code ,

       Desc,

       IF(SubStringCount(TempDesc,'SURGERY') , 'Surgery' , IF(SubStringCount(TempDesc,'LAB') , 'Lab' ,'Other') ) AS NewDesc

Resident TEMP;

DROP Table TEMP;

[Anonymous]

That worked perfectly! Thank you