Data Consolidation Question

[mccook]

Hi,

I'm looking for some advice on below please:

I have a table a little like below (with nearly 1 million rows of data):

Name	Rule 1	Rule 2	Rule 3	Rule 4
Dean	Y	N	N	N
Dean	N	Y	N	N
Lisa	Y	N	N	N
Dean	N	N	Y	N
Lisa	N	Y	N	N
Lisa	N	N	Y	N
Lisa	N	N	N	Y

I want to consolidate the lines and have a table like below:

Name	Rule 1	Rule 2	Rule 3	Rule 4
Dean	Y	Y	Y	N
Lisa	Y	Y	Y	Y

Reason being if the person has a 'Y' against a Rule, I'm not interested in the 'N'.

Any advice appreciated.

Cheers,

Dean

[alexpanjhc]

In your load script, if rule1='Y' set it to 1 else set in 0. do the same for rule,2,3,4

and you can add them together. If it is 1, then make it Y.

There you will get your new table.

[alexandros17 · Accepted solution]

You have big amount of data so I suggest to work in the script:

T1:

Load * inline [

Name, Rule 1, Rule 2, Rule 3, Rule 4

Dean, Y, N, N, N

Dean, N, Y, N, N

Dean, N, N, Y, N

Lisa, Y, N, N, N

Lisa, N, Y, N, N

Lisa, N, N, Y, N

Lisa, N, N, N, Y];

T2:

NoConcatenate

LOAD Name, MaxString([Rule 1]) as Rule1, MaxString([Rule 2]) as Rule2, MaxString([Rule 3]) as Rule3, MaxString([Rule 4]) as Rule4 Resident T1

Group By Name;

T2 is the table you need

Hope it helps

[marcus_sommer]

Try this:

t1:

Load

     Name,

     if(index([Rule 1], 'Y') >= 1, 'Y', 'N') as [Rule 1],

     if(index([Rule 2], 'Y') >= 1, 'Y', 'N') as [Rule 2],

     if(index([Rule 3], 'Y') >= 1, 'Y', 'N') as [Rule 3],

     if(index([Rule 4], 'Y') >= 1, 'Y', 'N') as [Rule 4];

Load

     Name,

     concat([Rule 1], ', ') as [Rule1],

     concat([Rule 2], ', ') as [Rule2],

     concat([Rule 3], ', ') as [Rule3],

     concat([Rule 4], ', ') as [Rule4]

From YOUR_TABLE Group by Name;

- Marcus

[mccook]

Perfect.

Thanks everyone.

[#6]

Hi Dean,

try the sample attached.

Regards