topic Re: Data Consolidation Question in QlikView

Data Consolidation Question

mccook — Wed, 26 Feb 2014 15:19:23 GMT

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

Re: Data Consolidation Question

alexpanjhc — Wed, 26 Feb 2014 15:24:57 GMT

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.

Re: Data Consolidation Question

alexandros17 — Wed, 26 Feb 2014 15:27:47 GMT

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

Re: Data Consolidation Question

marcus_sommer — Wed, 26 Feb 2014 15:37:16 GMT

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

Re: Data Consolidation Question

mccook — Wed, 26 Feb 2014 15:37:49 GMT

Perfect.

Thanks everyone.

Re: Data Consolidation Question

Wed, 26 Feb 2014 15:44:02 GMT

Hi Dean,

try the sample attached.

Regards