Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

darren_dixon
New Contributor III

Two rows to one

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 NumberTenancy NumberPerson Number
1001987651
1001987652
1002987643
1003987634
1003987635

Person

Person NumberLeadForenameSurnameEmailMobile Phone
1YesAlanSmith--
2NoAndreaSmith--
3YesGaryJones--
4YesPaulJones--
5NoPaulineJones--

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 NumberPerson 1Person 1 - EmailPerson 1 - Mobile PhonePerson 2Person 2 - EMailPerson 2 - Mobile Phone
100198765Alan SmithAndrea Smith
100298764Gary Jones
100398763Paul JonesPauline Jones

Thanks,
Darren

1 Solution

Accepted Solutions
Highlighted

Re: Two rows to one

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

View solution in original post

4 Replies

Re: Two rows to one

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

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;

Capture.JPG

Highlighted

Re: Two rows to one

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

View solution in original post

Re: Two rows to one

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