Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
haymarketpaul
Creator III
Creator III

Creating a single record from multiple differing user rows

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

EmailTelephoneMobileMembership#
1@me.com0123456789--
1@me.com-09876543216475
1@me.com0123456789-6475
2@me.com0837363535--
2@me.com083736353506388377369376

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

EmailTelephoneMobileMembership#
1@me.com012345678909876543216475
2@me.com083736353506388377369376

Any ideas greatly appreciated

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

4 Replies
sunny_talwar

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
Creator
Creator

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

How does that even work..

haymarketpaul
Creator III
Creator III
Author

Perfect - Thank You...again

sunny_talwar

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

Capture.PNG