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

[#1]

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.

[tresesco]

You could try loading with distinct like:

Load Distinct

          SalesID,

               ....

From <>;

[#3]

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

[tresesco]

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

[#5 · Accepted solution]

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;