topic Re: Do join and then calculation in QlikView

Do join and then calculation

userid128223 — Tue, 05 Mar 2013 16:40:48 GMT

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.

in table 1 ?
in table 2?
after table 2?
Before table 1 ?
Should I create a calculation table "Calculations:"and call these from resident Table1, since it is joined with Table2.
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.

Re: Do join and then calculation

jagan — Tue, 05 Mar 2013 16:45:15 GMT

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.

Re: Do join and then calculation

userid128223 — Tue, 05 Mar 2013 17:29:23 GMT

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.

Re: Do join and then calculation

userid128223 — Tue, 05 Mar 2013 17:32:48 GMT

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;

Re: Do join and then calculation

jagan — Wed, 06 Mar 2013 11:17:54 GMT

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.

Re: Do join and then calculation

userid128223 — Wed, 06 Mar 2013 15:03:50 GMT

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.

Re: Do join and then calculation

jagan — Wed, 06 Mar 2013 15:45:11 GMT

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.

Re: Do join and then calculation

userid128223 — Wed, 06 Mar 2013 16:58:22 GMT

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.