Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Split one column to 2 columns and reduce number of duplicate rows

Hi,


I have a set of data with both unique and repeating SalesIDs. On the the second column, is an identifier "1" or "0". Some sales Ids have both 1 and 0 but some have only 1 or 0. I want to have these data set in qlikview such that I have a single comlumn of unique SalesIDs and 2 new columns have "True" and "False" where each row could have a value either in both or only in 1. This way the duplication would be gotten rid from my data.

Any help how to achieve this.

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Split one column to 2 columns and reduce number of duplicate rows

Hi,

Do mean this:

Test:

LOAD * INLINE [

    SalesID, Identifier

    a, 1

    a, 0

    b, 1

    b, 0

    c, 1

    d, 0

    e, 0

    f, 1

    g, 1

    g, 0

];

Test2:

NoConcatenate

Load SalesID,

  if(Count(Identifier)=2,1,if(Sum(Identifier)=1,1)) as True,

  if(Count(Identifier)=2,0,if(Sum(Identifier)=0,0)) as False

Resident Test Group by SalesID;

The second Table will have unique rows of SaledID with two columns for true and False.

This would be easier though :

Test2:

NoConcatenate

Load SalesID,

  if(Index(Concat(Identifier),1)<>0,1) as True,

  if(Index(Concat(Identifier),0)<>0,0) as False

Resident Test Group by SalesID;

View solution in original post

4 Replies
MVP
MVP

Re: Split one column to 2 columns and reduce number of duplicate rows

You could try loading with distinct like:

Load Distinct

          SalesID,

               ....

From <>;

Highlighted
Not applicable

Re: Split one column to 2 columns and reduce number of duplicate rows

Thanks, but essentially I need 2 new columns with either true or false, in addition to distinct sales IDs. How can I do that?

Highlighted
MVP
MVP

Re: Split one column to 2 columns and reduce number of duplicate rows

Should not be that difficult but I am not very sure about requirement. Could you post a sample data and expected output against that?

Highlighted
Not applicable

Re: Split one column to 2 columns and reduce number of duplicate rows

Hi,

Do mean this:

Test:

LOAD * INLINE [

    SalesID, Identifier

    a, 1

    a, 0

    b, 1

    b, 0

    c, 1

    d, 0

    e, 0

    f, 1

    g, 1

    g, 0

];

Test2:

NoConcatenate

Load SalesID,

  if(Count(Identifier)=2,1,if(Sum(Identifier)=1,1)) as True,

  if(Count(Identifier)=2,0,if(Sum(Identifier)=0,0)) as False

Resident Test Group by SalesID;

The second Table will have unique rows of SaledID with two columns for true and False.

This would be easier though :

Test2:

NoConcatenate

Load SalesID,

  if(Index(Concat(Identifier),1)<>0,1) as True,

  if(Index(Concat(Identifier),0)<>0,0) as False

Resident Test Group by SalesID;

View solution in original post