align fields into one row

Tue, 07 Apr 2015 17:49:23 GMT

Hello All,

I have a spreadsheet with data like below:

Name sys_id Name Email Role

S1 123 Tom tom@abc.com 1st contact

S1 123 Lynn 2nd contact

S2 456 Sid sid@abc.com E-owner

S2 456 Manny man@abc.com 2nd contact

S2 445 Sid sid@abc.com E-owner

S2 445 Manny man@abc.com 2nd contact

and so on with a lot more possible values for roles.

During the load script we did a if(match) like in the script below to get the data into more columns based on a match on the Role field . so we should now have:

Name sys_id [1st contact Name] [1st contact Email] [2nd contact Name] [2nd contact Email] [E-owner Name] [E-owner Email]

S1 123 Tom tom@abc.com Lynn

S2 456 Manny man@abc.com Sid sid@abc.com

S2 445 Manny man@abc.com Sid sid@abc.com

However when we list the rows in a table field for e.g. to export back to excel we see we have the below results where the contacts are split into different rows for each Name-sysid combination:

Name sys_id [1st contact Name] [1st contact Email] [2nd contact Name] [2nd contact Email] [E-owner Name] [E-owner Email]

S1 123 Tom tom@abc.com - - - -

S1 123 - - Lynn - - -

S2 456 - - Manny man@abc.com

S2 456 Sid sid@abc.com

S2 445 Sid sid@abc.com

S2 445 Manny man@abc.com

How do I get the table to look like how we expect it to be, in one row with all the relevant fields based on the Name and sys_id.

My load script looks as below:

LOAD DISTINCT

Name,

sys_id,

if(Match([role],'1st contact'),[Name]) as [1st contact Name],

if(Match([role],'1st contact'),Email) as [1st contact Email],

if(Match([role],'2nd contact'),[Name]) as [2nd contact Name],

if(Match([role],'2nd contact'),Email) as [2nd contact Email],

if(Match([role],'E-owner'),[Name]) as [E-owner Name],

if(Match([role],'E-owner;'),Email) as [E-owner Email]

FROM

(ooxml, embedded labels, table is Page1_1);

Please advice. I tried doing a left join on self and a noconcatenate for a final table, but I haven't been able to get this right. Thanks for your help in advance.

Thanks,

Samira

Re: align fields into one row

prieper — Tue, 07 Apr 2015 19:04:17 GMT

you may a table with some calculations without transforming your data

emails would go the same way ....

HTH Peter

Re: align fields into one row

Tue, 07 Apr 2015 21:32:21 GMT

Peter,

The solution you provided worked on the sample data, however when I try to apply it to my main spreadsheet with about 60K rows, the straight table is not getting populated. Not sure whats causing the issue. I am able to see the data in a table box ,without the only(if) conditions of course, but it doesn't seem to like something in the chart. Below is what I have for the expression definition

for e.g. only(if(Role = 'First Call Support Contact', [full name]))

Any ideas on how I can troubleshoot further?

Thanks,

Samira

Re: align fields into one row

prieper — Wed, 08 Apr 2015 09:28:08 GMT

Cannot imagine that there are problems with just some 60,000 lines,

the ONLY is obsolete - QV uses it as default, when there is no aggregation specified.

You may create a SET-formula instead

aircode

ONLY({<Role = {'1st contact'} >} Name)

Peter

topic Re: align fields into one row in QlikView

align fields into one row

Re: align fields into one row

Re: align fields into one row

Re: align fields into one row