Friday afternoon dip, loose tables - Qlik Community

Report Inappropriate Content · ‎2011-05-20

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?

rbecher · ‎2011-05-20

Hi,

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

- Ralf

Astrato.io Head of R&D

Report Inappropriate Content · ‎2011-05-20

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

ToniKautto · ‎2011-05-20

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

rbecher · ‎2011-05-20

Do you use Qualify ...?

Astrato.io Head of R&D

Report Inappropriate Content · ‎2011-05-20

@ 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 · ‎2011-05-20

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

table1:

LOAD....

Astrato.io Head of R&D

ToniKautto · ‎2011-05-20

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

Report Inappropriate Content · ‎2011-05-20

Try This:

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

ToniKautto · ‎2011-05-20

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.