Two rows to one

[darren_dixon]

Hi,

Hope you can help.

I have two tables; History and Person and I am looking to put the two person records onto one line where they match with the property/tenancy.

History

Property Number	Tenancy Number	Person Number
1001	98765	1
1001	98765	2
1002	98764	3
1003	98763	4
1003	98763	5

Person

Person Number	Lead	Forename	Surname	Email	Mobile Phone
1	Yes	Alan	Smith	-	-
2	No	Andrea	Smith	-	-
3	Yes	Gary	Jones	-	-
4	Yes	Paul	Jones	-	-
5	No	Pauline	Jones	-	-

How do create the table to look like this where the Lead = Yes is always Person 1 and Lead = No is Person 2

Property Number	Tenancy Number	Person 1	Person 1 - Email	Person 1 - Mobile Phone	Person 2	Person 2 - EMail	Person 2 - Mobile Phone
1001	98765	Alan Smith			Andrea Smith
1002	98764	Gary Jones
1003	98763	Paul Jones			Pauline Jones

Thanks,
Darren

[its_anandrjs]

Try to make calculated field inside the Person table

Ex:-

PersonDetails:

Load

Person Number,

if(Lead = 'Yes', Forename &' '&Surname) as Person1,

if(Lead = 'Yes', Email) as Person1_Email,

if(Lead = 'Yes', [Mobile Phone]) as Person1_Phone,

if(Lead = 'No', Forename &' '&Surname) as Person2,

if(Lead = 'No', Email) as Person2_Email,

if(Lead = 'No', [Mobile Phone]) as Person2_Phone

Resident Person;

Regards

Anand

[Kushal_Chawda]

try this script solution

Data:

LOAD [Property Number],

     [Tenancy Number],

     [Person Number]

FROM

[https://community.qlik.com/thread/274419]

(html, codepage is 1252, embedded labels, table is @1);

Left Join(Data)

LOAD [Person Number],

     Lead,

     Forename,

     Surname,

     Email,

     [Mobile Phone]

FROM

[https://community.qlik.com/thread/274419]

(html, codepage is 1252, embedded labels, table is @2);

Final:

LOAD [Property Number],

     [Tenancy Number],

     Forename&' '&Surname as [Person 1 Name],

     Email as [Person 1 Email],

     [Mobile Phone] as [Person 1 Email Mobile Phone]

Resident Data

where Lead='Yes';

Left Join(Final)

LOAD [Property Number],

     [Tenancy Number],

     Forename&' '&Surname as [Person 2 Name],

     Email as [Person 2 Email],

     [Mobile Phone] as [Person 2 Email Mobile Phone]

Resident Data

where Lead='No';

DROP Table Data;

[Kushal_Chawda · Accepted solution]

Front end solution

create straight table

Dimension:

[Property Number],

[Tenancy Number]

Expression:

Person 1 Name

=only({<Lead={'Yes'}>}Forename) &' '&only({<Lead={'Yes'}>}Surname)

Person 1 Email

=only({<Lead={'Yes'}>}Email)

Person 1 Mobile Phone

=only({<Lead={'Yes'}>}[Mobile Phone])

Person 2 Name

=only({<Lead={'No'}>}Forename) &' '&only({<Lead={'No'}>}Surname)

Person 2 Email

=only({<Lead={'No'}>}Email)

Person 2 Mobile Phone

=only({<Lead={'No'}>}[Mobile Phone])

[its_anandrjs]

Another way on script part without join you can try this ways

PersonDetails:

Load

Person Number,

Forename &' '&Surname as Person1,

Email as Person1_Email,

[Mobile Phone] as Person1_Phone,

Resident Person Where Lead = 'Yes';

Concatenate(PersonDetails)

Load

Person Number,

Forename &' '&Surname) as Person2,

Email) as Person2_Email,

[Mobile Phone]) as Person2_Phone

Resident Person Where Lead = 'No';