topic Re: Two rows to one in QlikView

Two rows to one

darren_dixon — Wed, 13 Sep 2017 10:43:33 GMT

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 2
1001	98765	Alan Smith	Andrea Smith
1002	98764	Gary Jones
1003	98763	Paul Jones	Pauline Jones

Thanks,
Darren

Re: Two rows to one

its_anandrjs — Wed, 13 Sep 2017 10:58:06 GMT

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

Re: Two rows to one

Kushal_Chawda — Wed, 13 Sep 2017 10:59:09 GMT

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;

Re: Two rows to one

Kushal_Chawda — Wed, 13 Sep 2017 11:07:18 GMT

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])

Re: Two rows to one

its_anandrjs — Wed, 13 Sep 2017 11:14:34 GMT

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';