Do join and then calculation

[userid128223]

Hi Everyone

Can you please explain how following calculation can be done within script.

I have 2 tables that I have joined.  (default join = outerjoin)

ServiceDetails:

LOAD

CompanyName,

Salesrep,

HoursWorked,

ServiceDate,

Billable

FROM

Table_1;

// Join with above table

Join (ServiceDetails)

LOAD

CompanyName,

TimePurchasedDate,

TimePurchasedHours

FROM

Table_2;

Now lets say I want to do following calculation within script that I will be using number of times in my charts.

if (BILLABLE = 'T' and ServiceDate >= ADDMONTHS(TODAY(),-18), HoursWorked, 0) as BillableHrs,

if (BILLABLE = 'T' and TimePurchaseDate >= ADDMONTHS(TODAY(),-18), TimePurchasedHours, 0) as BillableTimePurchaseHrs;

Questions:

I am confused to where the calculation part goes and how qlikview goes thru the code. Where fields are available to be used in futher processing. Where do I add above calculation within script.

1. in table 1 ?
2. in table 2?
3. after table 2?
4. Before table 1 ?
5. Should I create a calculation table "Calculations:"and call these from resident Table1, since it is joined with Table2.
6. Whats the best approch.

Can someone please also explain how qlikview code gets executed and when each field has a value that can be used in further calculations.

[jagan]

Hi,

Try like this

ServiceDetailsTemp:

LOAD

CompanyName,

Salesrep,

HoursWorked,

ServiceDate,

Billable

FROM

Table_1;

// Join with above table

Join (ServiceDetailsTemp)

LOAD

CompanyName,

TimePurchasedDate,

TimePurchasedHours

FROM

Table_2;

ServiceDetails:

LOAD

     *,

     if (Billable= 'T' and ServiceDate >= ADDMONTHS(TODAY(),-18), HoursWorked, 0) as BillableHrs,

if (Billable= 'T' and TimePurchaseDate >= ADDMONTHS(TODAY(),-18), TimePurchasedHours, 0) as BillableTimePurchaseHrs

RESIDENT ServiceDetailsTemp;

DROP TABLE ServiceDetailsTemp;

Hope this helps you.

Regards,

Jagan.

[userid128223]

Thanks for the answer. For my understanding.

Can you please explain.

when are fields from tables 1 * 2 available for calculation within script.

is it after table 1 gets loaded

is it after table 2 gets loaded

is it before both tables.

[userid128223]

for example after I joined table 2 and did following would it work.

LOAD

if (Billable= 'T' and ServiceDate >= ADDMONTHS(TODAY(),-18), HoursWorked, 0) as BillableHrs,

if (Billable= 'T' and TimePurchaseDate >= ADDMONTHS(TODAY(),-18), TimePurchasedHours, 0) as BillableTimePurchaseHrs;

[jagan]

Hi,

In join statement it is not possible, because in the calculation you are using fields in both the tables.  Is the script given by me is working?  You have to do like that only.

Regards,

Jagan.

[userid128223]

Thanks Jagan

It works. Another question regarding TOTALS calculations.

When I use above table into a Pivot chart, I want to do totals by company which works.

however

when I do a subtotal on Project within Pivot table Presentation TAB. it does not work. it still gives me total by Company. Any way to fix this. Do I have to aggrigate.

[jagan]

Hi,

In Pivot Table -> Properties -> Presentation -> Select Dimension in Dimension and Expression -> Select Show Partial Sums.

Hope this is what you are expecting.

Regards,

Jagan.

[userid128223]

Thanks Jagan

I did that and the sum at Salesrep level & Project level comes out same. hence it is not working.

I wonder if i need to aggregate to get a project level total.