topic Re: Can expression be added in the script and reflected in the available field ? in QlikView

Can expression be added in the script and reflected in the available field ?

Tue, 11 Sep 2012 10:42:24 GMT

Hi All,

We prepared one pivot table where we used the below expression. Now the requirement is that this expression should be in the script so that we can have this expression as header. For your reference please look at the below attachment,

Sum(CostPerHour) / Sum (ExchRate1EURequals)

Now, we want this expression to be added in the script as CostPerHourInEUR.

Kindly help to get this done !!!!!

Regards

Nikhil

Re: Can expression be added in the script and reflected in the available field ?

Tue, 11 Sep 2012 10:43:59 GMT

doeas this two fileds belong to the same table?

if yes simply try to add this expression to that table loading script

Sum(CostPerHour) / Sum (ExchRate1EURequals) as CostPerHourInEUR

Re: Can expression be added in the script and reflected in the available field ?

Tue, 11 Sep 2012 10:45:47 GMT

Hi pari,

Thank you for your reply,

No, they do not belong to the same table.

I have used the left join between these two tables.

Regards

Re: Can expression be added in the script and reflected in the available field ?

Tue, 11 Sep 2012 11:09:25 GMT

you need to post script here (part with tables containing CostPerHour and ExchRate1EURequals

Re: Can expression be added in the script and reflected in the available field ?

Tue, 11 Sep 2012 11:19:39 GMT

Please find the script below,

The highlighted line I have added where it shows that the ExchRate1EURequals is missing.

Directory;
LOAD Country as BU1,
     Currency,
     Code,
     [Closing/Balance] as ExchRate1EURequals,
     [Ytd Aver./P&L],
     BU,
     Month,
     Year

FROM
[T1_Group Exchange Rates\GroupExchangeRates_to update.xlsx]
(ooxml, embedded labels, table is MonthlyTable);

Directory;
LOAD [Business Unit] as BU1,
     [Reporting Year],
     [Activity Detail (Travel)] as Travel,
     [# Claims (total)],
     [# Productive FTE (total)],
     [# Team leader],
     [# Manager],
     [# incoming calls],
     [# outgoing calls],
     [Total Calls],
     [Average Talktime],
     [Total yearly hours (per FTE)],
     [Vacation hours (per FTE)],
     [Public holidays hours (per FTE)],
     [Sick hours (per FTE)],
     [Other hours (per FTE)],
     Remarks,
     [Overtime hours (per FTE)],
     [Productive hours (per FTE)],
     [Total productive hours (all FTE)],
     [Claim per Productive hour],
     [Claim per FTE (Prod+TL)],
     [Production staff Costs per hour] as CostPerHour,
     [Currency (ISO3)],
     Comments,
     Year,
       [Production staff Costs per hour] / ExchRate1EURequals AS    CostPerHourInEUR
FROM
T2_CostsPerHour\CostsPerAvailableHour2011.xlsx
(ooxml, embedded labels, table is Travel);

Re: Can expression be added in the script and reflected in the available field ?

MayilVahanan — Tue, 11 Sep 2012 20:01:55 GMT

Try like this,

Join the both table as a single table. Then you can use

[Production staff Costs per hour] / ExchRate1EURequals AS CostPerHourInEUR in script..

Hope it helps

Re: Can expression be added in the script and reflected in the available field ?

Wed, 12 Sep 2012 07:06:29 GMT

Hi ,

Could you please advise how to join them,

I used the left join but still not working.

Is there any other way of joining both the table ?

Regards