Make two rows into one row on straight table

[#1]

Hi Everyone,

I am struggling with something which I know should be really easy.

I have a table which is the same as below. I only want a single entry for each key, but instead I am getting two lines. This is because there are two forms attached to the one key, which is acceptable.

key	form type	65+ fall ncon	65+, Fall	CAS110 Completed
001	101	Yes	Yes	No
001	110	No	No	Yes

How would I put this against a single line entry?

Thank you

[IAMDV]

Hi,

Which row do you want to show? Have you tried using the distinct clause?

Thanks,

DV

[#3]

I would like to have a single line for each Key, and then a colum for the form type being a 101, and another colum if the form type is a 110. In my databse I might have one key but several different form types attirubed to it. 

key	65+ fall ncon	65+ fall ncon	CAS101	CAS110 Completed
001	Yes	Yes	Yes	Yes

Thanks for the quick response.

[Jason_Michaelides]

Consider removing from type from the dimensions and creating 2 expressions:

FT101 - IF([from type]=101,[form type])

FT110 - IF([from type]=110,[form type])

Hope this helps,

Jason

[#5]

I'm not sure exactly what you would like to do. This might be helpful:

Dimension:

key

Expressions:

If (max(if([65+ fall ncon]='Yes','1','0'))>0,'Yes','No')

etc

[#6]

Hi,

You can do something like this.

If u have more rows, u can use max function.

C u,

Stefano

[jonathandienst]

Hi

To get the data from two sources into a single record, use JOIN. Something like this (please adapt to fit your data):

CombinedTable:
LOAD Key,
     'Yes' AS CAS101,
      ... (other fields from 101)
FROM Form101;
Left JOIN 
LOAD Key,
     'Yes' AS CAS110,
     ... (other fields from 110)
FROM Form110;

If any other field have the same name in both source tables, rename at least one of them (using AS) so that they are different. In this example, you want only Key to have the same name in both tables.

Hope that helps

Jonathan