topic Re: Merging two columns into one in QlikView

Merging two columns into one

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I am fairly new to Qlikview and I am trying to manipulate data in the scripting, rather than creating expressions later on. I have 15 columns that I need to place into 9 columns instead. Also, if a patient has more than 1 code in a specific category there are multiple rows for that individual. For instance a patient may have 2 rows under Metastatic Cancer with different dx codes. Here is a potential example for a patient:

Patient	Code	Metastatic Cancer	Acute Leukemia	Dementia
Patient A	ABC	1	0	0
Patient A	CDE	0	0	1
Patient A	FGH	0	1	0

I would like to combine the Metastatic Cancer and Acute Leukemia columns into one. Each column was given a 1 or 0 depending on whether or not the patient had a code that fell into that category. Therefore, I would like to ensure I capture if that one patient had 2 codes in the one bucket I create, if that is the case. The patient could also have a code in an unrelated other bucket. Can you please help me on the script formatting for this? I know once I group the columns I need to group by "Patient" but I am having a hard time combining the columns first. Does this make sense?

Re: Merging two columns into one

el_aprendiz111 — Thu, 29 Jun 2017 17:28:51 GMT

Hi,

tmp:
CrossTable(field, Data,2)
LOAD * Inline
[
Patient,Code,Metastatic Cancer,Acute Leukemia,Dementia,
Patient A,ABC,1,0,0,
Patient A,CDE,0,0,1,
Patient A,FGH,0,1,0,
];

Re: Merging two columns into one

Anonymous — Thu, 29 Jun 2017 17:56:10 GMT

You can also load it as an &

Load

Patient

Code

Mestatic cancer & Acute Lukemia as whatever name,

Dementia,

ect

From ....;

Re: Merging two columns into one

Anonymous — Thu, 29 Jun 2017 18:01:23 GMT

If you did want the cross table you can have:

Crosstable(Cancer/Lukemia, Data, 3)

Load

Patient,

Code,

Dementia,

Mestastic Cancer,

Lukemia,

From....;

The crosstable will count all the ones that are not going to be pivoted first and the pivot everything after that number. So if you have other column I would list them first then you can combine the ones you want.

Re: Merging two columns into one

Anonymous — Thu, 29 Jun 2017 19:41:34 GMT

Thank you Fer Fer but I don't want Patient A listed 9 times. I want the data to layout like this:

Patient Metastatic Cancer Dementia

Patient A 2 1

I will drop the code once I merge all the fields into the 9 columns. I only want Patient A listed once.

Re: Merging two columns into one

Anonymous — Thu, 29 Jun 2017 19:46:06 GMT

Thank you Kelly,

With that load, I will need to add a sum function in the case as shown above the patient has more than 1 code for a specific category or should I put "Count(Metastatic Cancer & Acute Leukemia) as new_name?

Re: Merging two columns into one

Anonymous — Thu, 29 Jun 2017 19:58:24 GMT

I'm pretty sure the sum will work. Sum(MetastaticCancer + Acute Lukemia) as new_name is what I'm pretty sure is the code.