topic Set Analysis and Concat in QlikView

Set Analysis and Concat

Fri, 06 Nov 2009 16:23:55 GMT

Hi, I have 2 tables like this:

table1:
LOAD * INLINE [
account, Amount
a, 1200
b, 500
c, 100
];

table2:
LOAD * INLINE [
Group, Accounts
g1, "a"
g2, "a,b,c"
g3, "b"
];

i use set analysis and concat, to group:

sum( {$< account = { $(=Concat(Accounts,',')) }>} Amount)

but the result is the total (1800)

[:S]

Thanks all for the help,

Fri, 06 Nov 2009 17:40:54 GMT

Hi,

is there a reason why your table could not look like this:

table2:
LOAD * INLINE [
Group, account
g1, a
g2, a
g2, b
g2, c
g3, b
];

Rainer

Fri, 06 Nov 2009 18:23:17 GMT

Hi Rainer,

There is not a real reason, is only a test structure for understand:

sum( {$< account = { $(=Concat(Accounts,',')) }>} Amount)

Thanks,

Fri, 06 Nov 2009 18:32:30 GMT

with the current structure of table2, this expression wont work..as in one Accounts, you have several of the variables 'account'.

if you change the structure of the table to what Rainer suggests, that should work..

vidyut — Fri, 06 Nov 2009 20:56:33 GMT

I looked at it, and find that the following expression in the table gives the desired result:

=Sum(If(Concat(total account,',')=Accounts,Amount,0))

Check if you are mixing your account and Accounts in the expression.

Sat, 07 Nov 2009 02:32:47 GMT

Rainer, Navee, Vidyut

if i changed the structure, work fine,now.

thanks a lot...