topic Re: count rows from two tables using parameters with different names in QlikView

count rows from two tables using parameters with different names

Anonymous — Thu, 18 Jan 2018 08:56:35 GMT

Suppose I have two following tables:

data1:

Load * inline

[

d1_ID, param1

A1, a

A2, b

A3, b

A4, c

A5, c

A6, e

A6, f

A7, f

];

data2:

Load * inline

[

d2_ID, d1_ID, param2

1, A1, a

2, A2, b

3, A2, c

4, A5, d

];

These tables are joined by d2_ID and both contain parameters (param1 and param2) with common values, but different name (in general I don't want to make a join by it). What I need is to count rows from data1 and data2 grouped by param1/param2, so in this case for example:

param d1_count d2_count

a 1 1

b 2 1

c 2 1

d 0 1

e 1 0

f 2 0

Any tips? I thought of making two temp tables with ID and param only (renaming param1 to param and param2 to param), then joining them using left join and treating this as a separate table... would that work?

Kasia

Re: count rows from two tables using parameters with different names

sunny_talwar — Thu, 18 Jan 2018 09:48:37 GMT

Try this

Script

data1:

LOAD * INLINE [

d1_ID, param1

A1, a

A2, b

A3, b

A4, c

A5, c

A6, e

A6, f

A7, f

];

data2:

LOAD * INLINE [

d2_ID, d1_ID, param2

1, A1, a

2, A2, b

3, A2, c

4, A5, d

];

Param:

LOAD DISTINCT param1 as param

Resident data1;

Concatenate (Param)

LOAD DISTINCT param2 as param

Resident data2;

And then this

Dimension

param

Expressions

=Count(DISTINCT If(param = param1, d1_ID))

=Count(DISTINCT If(param = param2, d2_ID))

Re: count rows from two tables using parameters with different names

Anonymous — Thu, 18 Jan 2018 10:15:12 GMT

Works like a charm! Thank you so much!