topic Re: How do I convert columns of data into rows - using QV Script in QlikView

How do I convert columns of data into rows - using QV Script

Anonymous — Tue, 24 Jan 2012 22:22:22 GMT

Hi,

I have a large 'fixed length' data file (TXT) that I am bringing into Qlikview. I am using the script to split out and label each field and that has worked well so far.

The data captures 100 possible procedure codes and 100 possible diagnosis codes (yes, the application is health).

I have to scan along these codes looking for certain matches, then apply a label to this patient's record accordingly. I believe this would be easier to do if the procedure and diagnosis codes were in their own table with a row for each one and a primary key linking back to the main patient record.

See below an example of the data as it currently resides in QV.

PrimaryKey

ProcCode 1

ProcCode 2

ProcCode 3

ProcCode 4

ProcCode 5

ProcCode 6

ProcCode 7

ProcCode 8

ProcCode 9

DiagCode 1

DiagCode 2

DiagCode 3

DiagCode 4

DiagCode 5

DiagCode 6

DiagCode 7

DiagCode 8

DiagCode 9

1001123

41819-00

30473-00

41801-00

2K22.2

2B37.81

1S27.83

1Y84.8

1Y92.22

1001124

36812-00

11900-00

2C61

2M8010/3

2C79.5

2M8010/6

2N32.8

1001125

90466-01

90481-00

2O70.0

1O71.82

2Z37.0

2Z72.0

1001126

2J44.1

1001127

2S32.83

2T08.0

2X59

2Y92.9

2U73.9

2J44.9

Say I am asked to identify where a patient has had a procedure of Adnoidectomy (41801-00). I have to build some sort of MATCH to identify and label this patient as Adnoidectomy. But I also have to check for numerous other procedure codes - just a select list. Now my MATCH will have to be nested into IF statements so that I end up with one new field that states what this patient had.

I think this these fields could be better tested if they ran down the page. ie:

PrimaryKey	Code	value
1001123	ProcCode 1	41819-00
1001124	ProcCode 1	36812-00
1001125	ProcCode 1	90466-01
1001123	ProcCode 2	30473-00
1001124	ProcCode 2	11900-00
1001125	ProcCode 2	90481-00
1001123	ProcCode 3	92515-29

What are your thoughts?

Can I pivot the data from a row to a column and then test it for a result? Once I've used the pivoted data I can drop it.

Or should I build a dirty great big nested MATCH statement and then test 100 columns of procedures and 100 columns of diagnosis codes?

Regards,

John.

How do I convert columns of data into rows - using QV Script

swuehl — Tue, 24 Jan 2012 22:42:54 GMT

John,

I think what you need here is a CROSSTABLE LOAD. Please check the Manual for details.

I believe it will probably look like

Crosstable (Code, Value) LOAD * resident Yourtable;

Hope this helps,

Stefan

Re: How do I convert columns of data into rows - using QV Script

Anonymous — Wed, 25 Jan 2012 00:33:31 GMT

Hi Stefan,

I have looked at the syntax for this command but am not sure how it would work.

For example: each row has primarykey,procedure1,procedure2,procedure3.....

How exactly would the CROSSTABLE Load look?

The Load Resident part is fine.

Regards,

John.

Re: How do I convert columns of data into rows - using QV Script

disqr_rm — Wed, 25 Jan 2012 00:43:26 GMT

Try this:

CrossTable(Code, Value)

LOAD PrimaryKey,

[ProcCode 1],

[ProcCode 2],

[ProcCode 3],

[ProcCode 4],

[ProcCode 5],

[ProcCode 6],

[ProcCode 7],

[ProcCode 8],

[ProcCode 9],

[DiagCode 1],

[DiagCode 2],

[DiagCode 3],

[DiagCode 4],

[DiagCode 5],

[DiagCode 6],

[DiagCode 7],

[DiagCode 8],

[DiagCode 9]

FROM

C:\Users\admin\Desktop\Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: How do I convert columns of data into rows - using QV Script

swuehl — Wed, 25 Jan 2012 00:45:39 GMT

I think it could look exactely like this as a line in your script:

Crosstable (Code, Value) Load * resident Yourtable;

Crosstable is a load prefix (like join etc.), it specifies a certain way to load the data.

If your Yourtable field structure looks like

primarykey,procedure1,procedure2,procedure3.....

it should work just fine. Just give it a try.

edit:

You could also create an excel with some line of your data, then use the table wizard (enter transformations -> Crosstable ) to play around and get familiar with the syntax.

edit2:

Or try this in a new script:

Crosstable (Code, Value) LOAD *

INLINE [

Key, Code1, Code2, Code3

1, a, x, a1

2, b, y, a2

3,c, z, a3

4,d, x, a4

5,e, z, a5

];

Re: How do I convert columns of data into rows - using QV Script

Anonymous — Wed, 25 Jan 2012 00:52:45 GMT

Ah, it does work!

I just need to make sure I have my primarykey as field number 1 then away it goes.

Brilliant!

Re: How do I convert columns of data into rows - using QV Script

Anonymous — Wed, 25 Jan 2012 00:53:35 GMT

both answers are correct.

thank you Gentlemen.