Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Friday afternoon dip, loose tables

Hello,

Maybe it's because it's almost weekend and I'm tired, but I don't know how to solve this.

I have created a mastercalendar because I need Periods instead of months.

So for this, I created an Excel file which has all the possible dates, the periodnumber en the number of weeks of that period.

So that means:

LOAD Datum as Date,

     Periode,

     Periode#weeks

FROM

DatesPeriod.xlsx

(ooxml, embedded labels, table is Blad1);

Also I have an Excel file which shows the number of FTE for each department in the same period.

LOAD @1 as Depnr,

     @2 as Department,

     @3,

     @4 as Periode,

     @5 as FTE

FROM

FTE1.xls

(biff, no labels, table is Blad1$);

Next I need all the bookedhours on their respective bookingdates, and the idea is that with the Posting Date as Date, I automatically get the right Period. But on Department. For example:

load
If (Department = 'PLAYOUT', 'BC PLAYOUT') as Department,
Datum as Date,
extern as BillableUren;

sql
select resg."Responsibility Center" as Department,
led."Posting Date" as Datum,
sum (led."Quantity") as extern

From Table X

As you may guess, I get a loop here and then some loosely coupled tables.

How can I make this work without a loop?

Tags (1)
18 Replies
rbecher
Not applicable

Friday afternoon dip, loose tables

Hi,

I'm not sure about your script but isn't there a missing GROUP BY Department, Datum in the SQL statement?

- Ralf

Not applicable

Re: Friday afternoon dip, loose tables

Hi Ralf.

No there is nothing missing, I just didn't copy the entire statement.

But to be precise:

load
If (Department = 'PLAYOUT', 'BC PLAYOUT') as Department,
Datum as Date,
extern as BillableUren;

sql
select resg."Responsibility Center" as Department,
led."Posting Date" as Datum,
sum (led."Quantity") as extern
from "$Job Ledger Entry" led, "$Resource Group" resg
where led."Job No_" like 'PL%'
and led."Quantity" > '0'
and led."Resource Group No_"  in ( 'UPMENS','UCMENS')
and led."Resource Group No_" = resg."No_"
and resg."Responsibility Center" = 'PLAYOUT'
group by resg."Responsibility Center", led."Posting Date";

Toni_Kautto
Not applicable

Friday afternoon dip, loose tables

Do you need the second and third table to be separate, or could you join them into one table?

rbecher
Not applicable

Re: Friday afternoon dip, loose tables

Do you use Qualify ...?

Not applicable

Re: Friday afternoon dip, loose tables

@ Toni,

The third is an actual database table, the second is an Excel file with 12 times the department name for every period, the periodnumber (1 till 12) and the FTE for each Period.

I don't know how I can combine them.

@Ralf.

No I don't use Qualify, I've read about this, but does it still 'link' my data?

rbecher
Not applicable

Re: Friday afternoon dip, loose tables

Can post more script code. Where are the table qualifiers?

table1:

LOAD....

Toni_Kautto
Not applicable

Re: Friday afternoon dip, loose tables

Friday indeed...

The quickest way that I can come up with is to separate department into one table and have one table for 'activities'. In the activity table you then add a field to flag the type of activity, like FTE or BillableUren. This would then require that you add a date stamp to your FTEs, but this could be done by for example setting them all to the max(Date) within the related period.

CALENDAR:

*Date,

Period

DEPARTMENT:

*DepNumber,

DepName

ACTIVITY

*DepNumber,

*Date,

ActivityType,

Value

Not applicable

Friday afternoon dip, loose tables

Try This:

Either Rename Department in the Excel file by someother name (which is different from that of Table) or vice versa.

Toni_Kautto
Not applicable

Friday afternoon dip, loose tables

I dont think QUALIFY or renaming is a good idea in this context, as then QlikView will no longer be able to determine that the Department identifiers are actually the same field.