Merging two columns into one

[Anonymous]

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?

[el_aprendiz111]

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,
];

[Anonymous]

You can also load it as an &

Load

    Patient

    Code

   Mestatic cancer & Acute Lukemia as whatever name,

   Dementia,

   ect

From ....;

[Anonymous]

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.

[Anonymous]

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. 

[Anonymous]

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?

[Anonymous]

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