Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

alindquist
New Contributor III

Give one description based on hierarchy

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 #CodeDescWhat I want Desc to Be
112345SurgerySurgery
156789LabSurgery
111111OtherSurgery
298765LabLab
211111OtherLab

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

1 Solution

Accepted Solutions
Not applicable

Re: Give one description based on hierarchy

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;

5 Replies
Not applicable

Re: Give one description based on hierarchy

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 ;

Not applicable

Re: Give one description based on hierarchy

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 ;

alindquist
New Contributor III

Re: Give one description based on hierarchy

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.

Not applicable

Re: Give one description based on hierarchy

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;

alindquist
New Contributor III

Re: Give one description based on hierarchy

That worked perfectly! Thank you