topic Re: joining tables in QlikView

joining tables

Anonymous — Tue, 01 Sep 2015 08:56:03 GMT

Hi,

I'd like to join two tables.

Table1:

Category, ClientNo

1, 10

1, 15

1, 7

2, 8

2, 9

2, 1

3, 20

3, 25

3, 30

Table2:

Category, Budget

1, 100

2, 500

3, 700

The result which I need:

Category, ClientNo, Budget

1, 10, 100

1, 15, -

1, 7, -

2, 8, 500

2, 9, -

2, 1, -

3, 20, 700

3, 25, -

3, 30, -

Here the budget is joined to each category only once.

I cannot guess how I should write the script in order to get such a result.

Could you please help?

Thank you in advance,

Larisa

Re: joining tables

alexandros17 — Tue, 01 Sep 2015 09:00:08 GMT

The join automatically links the budget to each row , give us a rule to associate budget to the category and client no.

Re: joining tables

lironbaram — Tue, 01 Sep 2015 09:01:21 GMT

why do you need to join the tables ?

in this case it seems very logical to leave two different tables connected by the category field

Re: joining tables

Anonymous — Tue, 01 Sep 2015 09:09:18 GMT

- Each budget value should be joined to the first row where the corresponding category values appears.

- In the resulting table each category value should be assigned to the budget value only once. It should not be possible to duplicate.

- ClientNo field should not be taken into consideration while joining the tables. It doesn't matter.

Re: joining tables

sunny_talwar — Tue, 01 Sep 2015 09:10:38 GMT

Try this:

Mapping:

Mapping

LOAD * Inline [

Category, Budget

1, 100

2, 500

3, 700

];

Table:

LOAD * Inline [

Category, ClientNo

1, 10

1, 15

1, 7

2, 8

2, 9

2, 1

3, 20

3, 25

3, 30

];

NewTable:

LOAD Category,

ClientNo,

ApplyMap('Mapping', Cat1) as Budget;

LOAD *,

If(Previous(Category) = Category, Null(), Category) as Cat1

Resident Table

Order By Category;

DROP Table Table;

Output:

Re: joining tables

Anonymous — Tue, 01 Sep 2015 09:11:19 GMT

I am aware of this possibility. But I'm trying to covert a snowflakes scheme to a star scheme. So, I'm trying to consolidate fact tables where possible.

Re: joining tables

Michiel_QV_Fan — Tue, 01 Sep 2015 09:13:29 GMT

The budget will be linked to each line.

In your expression you do not need to add sum(Budget), just add Budget to the expression field.

Or, add avg(Budget). In this case the Budget amount will always be devided by the number of rows corresponding to the dimensions.

Please also check out this blogpost: Fact Table with Mixed Granularity

Re: joining tables

alexandros17 — Tue, 01 Sep 2015 09:15:39 GMT

this is the script you need, let me know

Table1:
LOAD *, RowNo() as riga;
LOAD * Inline [
Category, ClientNo
1, 10
1, 15
1, 7
2, 8
2, 9
2, 1
3, 20
3, 25
3, 30
];

Left Join
LOAD Category, Min(riga) as riga, '1' as flag Resident Table1 Group By Category;

Left Join

LOAD * Inline [
Category, Budget
1, 100
2, 500
3, 700
];

Final:
NoConcatenate
LOAD Category, ClientNo, If(flag='1', Budget, Null()) as Budget Resident Table1;
DROP Table Table1;

Re: joining tables

martinpohl — Tue, 01 Sep 2015 09:21:13 GMT

Hello Larisa,

in that case that you want to consolidate your facts in one table do that:

Table1:

Category, ClientNo

1, 10

1, 15

1, 7

2, 8

2, 9

2, 1

3, 20

3, 25

3, 30

concatenate (Table1)

Category, Budget

1, 100

2, 500

3, 700

You will get one fact table.

The category is filled in all rows, the budget only where a category is,

the client number where the data is.

You can only compare those datas all fields are with datas in.

In a table box you will see all clients with all budgets, but if you do an expression, there is only one budget for each category.

Regards