Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

lornafnb
Valued Contributor

Design issue

hi guys

I am developing an HR app.

I basically have Headcount data and Leave data (a lot more leave data - thus use it to generate calendar).

something doesnt look right to me....like the data is not linking correctly..

can you please assist?

Thanks!

username = admin

password = fnb14

1 Solution

Accepted Solutions
Employee
Employee

Re: Design issue

Yes, it can be a little complex... a good plug for the official Developer course which covers this kind of thing.

Looking a bit more closely you already appear to have create a unique key so you might try to re-use this.

1. In FACT - Headcount amend the PERIOD field to make it unique to this table

LOAD MonthKey as HEADCOUNT_PERIOD,

Repeat this for the FACT - Leave table to create LEAVE_PERIOD

This should leave us with a single value to link between the tables. Now we need to re-attach the calendar

2. We need to load all of the HEADCOUNT_PERIOD and LEAVE_PERIOD into a new table and associate it with your KEY

PERIOD_KEY_LINK

LOAD HEADCOUNT_PERIOD as PERIOD,

     KEY

Resident FACT_HEADCOUNT;

LOAD LEAVE_PERIOD as PERIOD,

     KEY

Resident FACT_LEAVE;

3. Finally, tweak your calendar script to use the new PERIOD_KEY_LINK table to provide the PERIOD field

Temp_Calendar_Range:

    LOAD

     Num(Date#(Min(PERIOD), 'YYYYMM')) as MinDate,

        Num(Date#(Max(PERIOD), 'YYYYMM')) as MaxDate

    RESIDENT PERIOD_KEY_LINK;

Unfortunately I can't reload to test this but I think the logic is correct.

8 Replies
Highlighted
Employee
Employee

Re: Design issue

Hi Lorna,

Having a quick look it is hard to tell what is is that is not right, do you have an example? Looking at the Table Viewer I can see that you have a synthetic key on the PERIOD and KEY fields. Although a synthetic key can be acceptable in a data structure it is a good idea to remove it. To do this you might

1. Differentiate both KEY and PERIOD between the two tables where the duplicate fields exist, probably by renaming them (e.g. KEY -> LEAVE_KEY and HEADCOUNT_KEY) or drop them if they are not required in these tables.

2. Create a link table for PERIOD, KEY and PERIOD_KEY

3. Create a composite key in FACT_LEAVE and FACT_HEADCOUNT concatenating the KEY and PERIOD values to link to the link table.

lornafnb
Valued Contributor

Re: Design issue

thanks Adam....great suggestion...though I need some coding assistance please....

lornafnb
Valued Contributor

Re: Re: Design issue

is this better?

Employee
Employee

Re: Design issue

Yes, it can be a little complex... a good plug for the official Developer course which covers this kind of thing.

Looking a bit more closely you already appear to have create a unique key so you might try to re-use this.

1. In FACT - Headcount amend the PERIOD field to make it unique to this table

LOAD MonthKey as HEADCOUNT_PERIOD,

Repeat this for the FACT - Leave table to create LEAVE_PERIOD

This should leave us with a single value to link between the tables. Now we need to re-attach the calendar

2. We need to load all of the HEADCOUNT_PERIOD and LEAVE_PERIOD into a new table and associate it with your KEY

PERIOD_KEY_LINK

LOAD HEADCOUNT_PERIOD as PERIOD,

     KEY

Resident FACT_HEADCOUNT;

LOAD LEAVE_PERIOD as PERIOD,

     KEY

Resident FACT_LEAVE;

3. Finally, tweak your calendar script to use the new PERIOD_KEY_LINK table to provide the PERIOD field

Temp_Calendar_Range:

    LOAD

     Num(Date#(Min(PERIOD), 'YYYYMM')) as MinDate,

        Num(Date#(Max(PERIOD), 'YYYYMM')) as MaxDate

    RESIDENT PERIOD_KEY_LINK;

Unfortunately I can't reload to test this but I think the logic is correct.

Employee
Employee

Re: Design issue

Ah, you just beat me to it. Yes, that is looking better. You just need to run your calendar script on the new PERIOD field in JOIN_TABLE. Does it work any better, are you still seeing issues with the data?

lornafnb
Valued Contributor

Re: Design issue

Thank you so much Adam

lornafnb
Valued Contributor

Re: Re: Design issue

Adam,

because of the data being so disparate (in months) how can I test for NULLs - so at least something displays instead of NULLS.....

(e.g. the Race field)...

thanks!

updated model included

Employee
Employee

Re: Re: Design issue

Maybe look into using the NullAsValue() statement to convert the nulls into something more friendly, such as 'na'. The QlikView helpfile describes it quite nicely so I won't try to better it.