I have to sets of data that I need and do not know how to use. One table (excel spreadsheet) includes a list of cases and its associated visit information. The second table (second excel spreadsheet) is a reference table that includes list of diagnoses of interest.
In the cases table with all the "raw data", I have 5 columns referencing the case's diagnoses (Diag1, Diag2, Diag3, Diag4, Diag5). The reference table only has 3 columns (Diag_Code_No, Diag_Group, Diag_Description).
What I need:
I need to create a table or expression or variable that I can use that uses the diagnosis reference table and assigns one of the Diag_Group to each case based on those 5 columns from the cases table.
If more than one Diag-Group applies, it will only assign the one with the the highest ranked Diagnosis. For example, if one case has a Diagnosis of choice in Diag1 and Diag 3, the Diag-Group assigned to it will be for Diag1 as this is considered the primary diagnosis.
How can I accomplish this or what tutorial can I reference to get started?
Attached is some sample data and what I need it to look like. I need the final table to have a column that assigns the Grouped ICD9 code to that line item (visit) based on the reference spreadsheet.
I believe it is a form of concatanate/join, but am unsure how to do it, how to script it (syntaxt), etc.
I see what you mean and why it is hard. I have made changes to the reference table so that it would have the Diagnosis1 column match by name. The formats are the same too, so it should not be an issue.
I have attached the updated documents.
What I thought should work was the following, but I know it is wrong and need help finishing up. The idea is to make this the load script that would create the master combined table that will later be loaded as a separate file.
LOAD* // Note here that DiagCode1 is a field in this data FROM (ooxml, embeddedlabels, tableis Sheet1);
ReferenceTbl: LeftJoin (RawData) LOADDiagCode1, // Note that this is the column the two tables will have in common to join on Group,
[ICD-9 Code Description] FROM
(ooxml, embeddedlabels, tableis Final); STORE ReferenceTbl into(txt);
Earlier I assumed Diag No and your ICD-9 Code can be joined but from your shared files I couldn't see possibility of linking them. I tried to see similarities between 'Group' Column and 'Diag1_Description' column but its not helping as both are created independently with no intention to keep them aligned with each other, so difficult to link.
In Qlikview if two tables are having fields with same name, they are internally joined automatically. So somehow we need to find related field from business perspective between your tables so that we can join them.
I still feel Diag# and 'ICD-9 Code' should have been linked by some way but I am not able to figure out from the information shared so far.