joining tables

[Anonymous]

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

[alexandros17]

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

[lironbaram]

hi

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

[Anonymous]

- 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.

[sunny_talwar]

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:

[Anonymous]

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.

[Michiel_QV_Fan]

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

[alexandros17 · Accepted solution]

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;

[martinpohl]

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