Create field that contains all combinations of two fields

[#1]

Hello.

I am trying to create a new table from two other fields by taking all combinations of them.

I have one field, CODE in the table DEFINITION_CODE that maps to field PHENO_VALUE in table PHENOTYPES via the id PHENO_DEFINITION_CODE_ID. These maps to a specific DONOR_ID in the table DONOR. (See the attached example)

CODE contains phenotype codes (A, C, Fya, Ena and so on) and PHENO_VALUE contains values that these CODES can have (POSITIVE, NEGATIVE, INCONCLUSIVE).

I want to take these two fields and generate a new field with all combinations (A+,A-,A?,C+,C-,C?,Fya+ and so on).

I am attaching an example with the fields.

Thank you for any help!

/Databyran

[Anonymous]

A cartesian join should help you here:

1, Load in one of the fields

Tab1:

LOAD

    CODE AS NewCode

RESIDENT DEFINITION_CODE;

2. Join on the other field, but without any common field to link it off of

LEFT JOIN (Tab1)

LOAD

    PHENO_VALUE AS New_PhenoValue

RESIDENT PHENOTYPES;

3. Combined the fields

LEFT JOIN (Tab1)

LOAD

    NewCode&New_PhenoValue AS CombinedCode

RESIDENT Tab1

Hopefully something like that should work.

Jonathan

[#3]

Do you mean that when you select (for example) phnotype_id = 16117492 that you like to see a new field that states (Cw-)?

[#4]

Yes, exactly! And the other way around, if I choose Cw- I want to see all phenotype_id that have Cw-!

[#5]

So the easy way to do that in your existing script is to add this to the end of the script:

Donor_PhenoTypesNew:

LOAD *

, PHENOTYPE_CODE&IF(PHENOVALUE='Positive','+',IF(PHENOVALUE='Negative','-','?')) AS NewColumnName

RESIDENT Donor_Phenotypes;

DROP TABLE Donor_Phenotypes;

[rustyfishbones]

Create a List Box Box Expression

and simply say

=PHENOTYPE_CODE&PHENO_VALUE