Creating a single record from multiple differing user rows

[haymarketpaul]

QV12 SR3

What is the best way to consolidate user data so i have just one record per email with whichever field is populated

eg  if i have this sort of thing in a file

Email	Telephone	Mobile	Membership#
1@me.com	0123456789	-	-
1@me.com	-	0987654321	6475
1@me.com	0123456789	-	6475
2@me.com	0837363535	-	-
2@me.com	0837363535	0638837736	9376

i want to end up with this sort of thing so i have one row per email with data wherever data is populated in a field for the user

Email	Telephone	Mobile	Membership#
1@me.com	0123456789	0987654321	6475
2@me.com	0837363535	0638837736	9376

Any ideas greatly appreciated

[sunny_talwar · Accepted solution]

Try something like this:

Table:

LOAD Email,

    MinString(Telephone) as Telephone,

    MinString(Mobile) as Mobile,

    MinString(Membership#) as Membership#

FROM

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

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

Group By Email;

In this particular scenario, MinString worked because '-' were read as  '-'. If you have true Nulls, then try with MaxString

Table:

LOAD Email,

    MaxString(Telephone) as Telephone,

    MaxString(Mobile) as Mobile,

    MaxString(Membership#) as Membership#

FROM

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

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

Group By Email;

[jjbom1990]

Did you just extract the data from the table that is posted in this thread?

How does that even work..

[haymarketpaul]

Perfect - Thank You...again

[sunny_talwar]

There is an option to load data from the 'Web File...' in the data load editor