topic Re: data model in QlikView

data model

master_student — Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

My data model generates loops, here's attached.

Could you please help me to remove loops?

Thanks in advance

Re: data model

swuehl — Sun, 15 May 2016 19:53:45 GMT

That's hard to do without knowing your model in detail.

In general, consider renaming fields that are used for different roles of the same entity:

Circular References

Re: data model

master_student — Sun, 15 May 2016 22:11:40 GMT

Hello,

Thanks for your response.

I will try to explain to you my model.

so I have a budget (excel file) that contins the departments budget LBopex: the name of the dep as the business users named them

I also use a database that contains the LB : name of dep in the database

so you can remark that LB is not exactly LBopex

To fix that I used a map table that contains the LBopex and its equivalent LB

the objective is to have a table that contains LBopex/LB/mesaures from the buget excel file/measures from the database.

please excuse my bad english.

Thanks

Re: data model

ziadm — Mon, 16 May 2016 01:27:55 GMT

The Data Model has Synthetic keys DateID ,CDR and OPEX Category. These Fields are duplicated in the Tables Budget, Map and BC. I would suggest rename the DateID field in the BC Table to different name and rename the field OPEX in the Table map. This does not mean you that your data model is would be a working model. Try to creat a unique kets using Autonumber function to link two tables.

Re: data model

master_student — Mon, 16 May 2016 08:06:56 GMT

Hi,

I can't rename Date ID field, I am using it as a link to the Calendar dimension. any suggest to cretae a link table?

Thanks

Wiem

Re: data model

swuehl — Mon, 16 May 2016 08:25:56 GMT

Your map table, was that used for the mapping only? Maybe you have forgot to drop the table at the end?

If this is the case, this may solve the circular loop already.

Re: data model

master_student — Mon, 16 May 2016 09:29:58 GMT

if I drop the map tab, how can I get the corresponding LB/LBopex

Re: data model

swuehl — Mon, 16 May 2016 15:36:24 GMT

That's hard to answer without knowing more about your model.

Can you post your current script?

Re: data model

master_student — Mon, 16 May 2016 15:50:05 GMT

here is my script :

//excel budget file

budget:

LOAD [%Date ID],

OpEx_Category,

Direction,

CDR_Name,

Type

,trim(lower(LBopex))

,CDR

,Mois

Resident PO_BUDGET;

//maptable

map:

LOAD OpEx_Category,

trim (lower([LB / Budget] ))as LBopex,

trim(lower([LB / ERP])) as LB

FROM

xxxx

//calendar dimension

calendar:

[%Date ID],

Year,

month,

day

calendar date

//table comming from the database

BC:

LOAD trim(lower( LB)),

CDR,

[Date_PO]

AutoNumber( MakeDate( year(date([Date_PO],'DD/MM/YYYY')), Month(date([Date_PO],'DD/MM/YYYY')), day(date([Date_PO],'DD/MM/YYYY')) ) , '%Date ID') as [%Date ID]

Resident SUIVI_DA_BC_tmp;

I need to join the LB FROM database with LBopex FROM EXCEL file to get the measures in the database that's why I am using a map table but it generates loops

Thanks in advance

Re: data model

swuehl — Mon, 16 May 2016 16:01:32 GMT

Try to avoid the map table in the data model, instead, use a MAPPING approach to replace the Excel LBOpex values with the corresponding value from the database, e.g. like shown here:

Data Cleansing‌

If LBOpex and LB are both representing your departments for budget and BC, you should end up with naming the fields in both tables the same.

This might create a synthetic key, but no circular loop anymore (if you've dismissed the map table).

You can then remove the synthetic key replacing it with a combined key in a second step.

Re: data model

master_student — Mon, 16 May 2016 16:11:19 GMT

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

Re: data model

swuehl — Mon, 16 May 2016 16:26:10 GMT

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

Re: data model

master_student — Mon, 16 May 2016 16:29:27 GMT

exactly, the sum budget will be wrong

Re: data model

swuehl — Mon, 16 May 2016 17:13:02 GMT

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?

Re: data model

master_student — Mon, 16 May 2016 20:12:08 GMT

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

Re: data model

swuehl — Mon, 16 May 2016 20:27:57 GMT

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
consulting fees	software	3	3
Total		5	8

Re: data model

master_student — Mon, 16 May 2016 20:36:04 GMT

it works but there is redundancy. see bellow :

an other screenshot of the result i get :

Thanks for your help.

Re: data model

swuehl — Mon, 16 May 2016 21:22:32 GMT

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)?

Re: data model

master_student — Mon, 16 May 2016 21:30:32 GMT

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

Re: data model

qliksus — Tue, 17 May 2016 08:21:02 GMT

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
];