data model

[master_student]

Hello everyone,

My data model generates loops, here's attached.

Could you please help me to remove loops?

Thanks in advance

[master_student]

the Mapping approch is used in case we have a one to one relation. in my case an LBopex could match with one or more LB

Thanks

[swuehl]

And what would be the consequence then for your budget values, w.r.t. assigning budget to departments?

[master_student]

exactly, the sum budget will be wrong

[swuehl]

Sorry, I am getting a little confused.

Would it be possible that you create two simplified excel sheets showing your input data and maybe another sheet with an expected result report?

[master_student]

budget (excel file)

date           LBopex           budget

01/01/2016 consulting fees  3

01/02/2016 admin fees        2

maptable

LBopex            LB

consulting fees  serviceit

consulting fees  software

consulting fees desktop

admin fees        admin fees

BC (database)

LB       mantant po

Service it   2

software   3

admin fees 3 ...

The result is :

LBopex                       LB             sum(budget)=3    sum(montant po) =8

CONSULTING fees      service it  3                             2

                                    software  3                             3

                                     admin fees  3                          3

Is it more clear now?

Thanks for your help

[swuehl]

Maybe like this to get rid of the map table?

budget:
LOAD * INLINE [
date,          LBopex,          budget
01/01/2016, consulting fees,  3
01/02/2016, admin fees,        2
];


maptable:
Mapping
LOAD LB, LBopex INLINE [
LBopex,            LB
consulting fees,  service it
consulting fees,  software
consulting fees, desktop
admin fees,        admin fees
];


BC:
LOAD *, ApplyMap('maptable',LB) as LBopex INLINE [
LB,      mantant po
service it,  2
software,  3
admin fees, 3
];

LBopex	LB	Sum(budget)	sum([mantant po])
admin fees	admin fees	2	3
consulting fees	service it	3	2
	software	3	3
Total		5	8

[master_student]

it works but there is redundancy. see bellow :

an other screenshot of the result i get :

Thanks for your help.

[swuehl]

Your tables don't seem to link correctly, but it's hard to see what's wrong just looking at the screen shots.

Could you upload your current QVW (or the one I've posted with your updated model)?

[master_student]

swuehl‌ , Hello, I just add the map table in all tables, so for each dimesion I have LB and LBopex.

I hope the data will be correct. will let you know.

Thanks again for your help

[qliksus]

Hi ,

Should the join be based on only LB ? or it can be both LB&Date of both the Budget table and DB Measure table  . If it can be both you can simply Left join the Map table and connect both the tables using the  LB&Date. If you cant connect it through LB&Date then you can create a link table something like the below

budget: 
LOAD * INLINE [ 
datebud,LBopex, budget 
01/01/2016, consulting fees,  3 
01/02/2016, admin fees,        2 
]; 

Left Join (budget)
LOAD * INLINE [  
    LBopex,LB
    consulting fees,service it 
    consulting fees,software 
    consulting fees,desktop 
    admin fees,admin fees 
];

BC: 
LOAD * INLINE [ 
LB,mantant po,datebc 
service it,2 , 01/01/2016
software,3 ,01/02/2016
admin fees,3 ,01/03/2016
]; 

Link:
load  datebud as date_link ,LB
Resident budget ;
Concatenate (Link)
load  datebc as date_link ,LB
Resident BC ;

Calendar:
LOAD * INLINE [ 
date_link
01/01/2016
01/02/2016
01/03/2016
01/04/2016
];