topic Re: How to combine two separate tables in QlikView

How to combine two separate tables

Fri, 25 Sep 2015 18:40:03 GMT

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?

Thank you!

-------------------------

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.

RawData:

LOAD * // Note here that DiagCode1 is a field in this data
FROM

(ooxml, embedded labels, table is Sheet1);

ReferenceTbl:
Left Join (RawData)
LOAD DiagCode1, // Note that this is the column the two tables will have in common to join on
Group,

[ICD-9 Code Description]
FROM

(ooxml, embedded labels, table is Final);
STORE ReferenceTbl into (txt);

Thank you.

Re: How to combine two separate tables

qlikviewwizard — Sat, 26 Sep 2015 02:15:47 GMT

Hi Karen,

In general , we can combine two tables by using join,concatenate functions.

Please attach some sample data of two tables in a excel format.

Re: How to combine two separate tables

Digvijay_Singh — Sat, 26 Sep 2015 05:22:17 GMT

Check if attached working can help you to kick-start -

Directory;

CrossTable(DiagNo, Diag_Code_No, 2)

LOAD Case#,

[Case Desc],

Diag1,

Diag2,

Diag3,

Diag4,

Diag5

FROM

[..\..\Case Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

RefTable:

Load * Inline [

Diag_Code_No,Diag_Group,Diag_Description

1,Group1,Fever Mild

2,Group1,Fever Medium

3,Group1,Fever High

4,Group2, Malaria Low

5,Group2, Malaria Medium

6,Group2, Malaria Low

7,Group3, Lever layer prob

8,Group3, Lever Core prob ]

case excel used is -

Case#	Case Desc	Diag1	Diag2	Diag3	Diag4	Diag5
1	Temperature High	1	2	3	4	5
2	Cough Problem	4	5	6	7	8

Re: How to combine two separate tables

Digvijay_Singh — Fri, 02 Oct 2015 04:04:40 GMT

I checked the new attachments shared.

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.

Re: How to combine two separate tables

Digvijay_Singh — Fri, 02 Oct 2015 17:04:15 GMT

AFAIK Your script should be working fine as long as you have linked/valid data in both tables through DiagCode1.