Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | 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
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])
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
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;
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])
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';