Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
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;

View solution in original post

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