Column Transformation in Qlik sense

[arusanah]

Here is sample of my data.  There are two columns. 2nd column has multiple sections

Second Digit - Type of Facility		Third Digit - Bill Classification
1 Hospital		Second Digit = 1 (Hospital)
2 Skilled Nursing		1 Inpatient (Including Medicare Part A)
3 Home Health		2 Inpatient (Medicare Part B Only)
4 Religious Non-Medical		3 Outpatient
6 Intermediate Care		4 Laboratory Services Provided to Non-patients
7 Clinic		8 Swing Beds
8 Special Facility or ASC Surgery
		Second Digit = 2 (Skilled Nursing)
		1 Inpatient (Including Medicare Part A)
		2 Inpatient (Medicare Part B Only)
		3 Outpatient
		8 Swing Beds

My requirement is to generate 3rd column based on the values from the First two columns. For e.g. if from first-column value comes to 1 then from the second it falls under the first section then it will check under that section what type of bill it is and generates a code. 

Desired Output : 

it looks like I should be using a hierarchy approach, but due to time constraints I'm having a hard time figuring it out, so need expert help.

Your help is much appreciated!

[sidhiq91]

@arusanah  Could you please provide us actual data how it looks? So that we can help you to resolve your issue.

[arusanah]

hello Sidhiq,

I have excel source file like this 

Second Digit - Type of Facility		Third Digit - Bill Classification
1 Hospital		Second Digit = 1 (Hospital)
2 Skilled Nursing		1 Inpatient (Including Medicare Part A)
3 Home Health		2 Inpatient (Medicare Part B Only)
4 Religious Non-Medical		3 Outpatient
6 Intermediate Care		4 Laboratory Services Provided to Non-patients
7 Clinic		8 Swing Beds
8 Special Facility or ASC Surgery
		Second Digit = 2 (Skilled Nursing)
		1 Inpatient (Including Medicare Part A)
		2 Inpatient (Medicare Part B Only)
		3 Outpatient
		8 Swing Beds