topic Re: Creating a single record from multiple differing user rows in QlikView

Creating a single record from multiple differing user rows

haymarketpaul — Wed, 25 Nov 2020 16:16:04 GMT

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 — Wed, 27 Jul 2016 10:32:26 GMT

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 — Wed, 27 Jul 2016 11:04:15 GMT

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

How does that even work..

haymarketpaul — Wed, 27 Jul 2016 11:26:41 GMT

Perfect - Thank You...again

sunny_talwar — Wed, 27 Jul 2016 12:16:41 GMT

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