topic Re: Problem with a script in QlikView

Problem with a script

stark___ — Thu, 04 Apr 2013 13:25:26 GMT

Hello,

I have a script like this:

Sales_Register:
LOAD
"_period" as Period,
'кв.'&ceil(month(date("_period"))/3)as Quarter,
Day(Date("_period")) as Day,
month(date("_period")) as Month,
Year(date("_period")) as Year,
"_fld4843rref" as Product,
"_fld4847rref" as Distributor,
"_fld4851" as Quantity,
"_fld4852" as Sales,

SQL SELECT

"_period",
"_fld4843rref",
"_fld4847rref",
"_fld4851",
"_fld4852"
FROM "ut82sql".public."_accumrg4842";

LOAD

Product,
DirectCost

FROM
D:\DirectCost.xls
(biff, embedded labels);

and I want to create new dimension "Cost" that calculated like "Quantity*DirectCost ", but I don't know how to do this.

Can anyone help me to create it in the script?

Best regards, Stark

Re: Problem with a script

Thu, 04 Apr 2013 20:13:19 GMT

Hi Stark,

if your product matches in both tables you can merge the two fields from the second table into the first using a Qlikview Join (not quite the same to SQL Join).

left join (Sales_Register)

LOAD

Product,
DirectCost

FROM
D:\DirectCost.xls
(biff, embedded labels);

alfterwards resident load similar to this:

NewSales_Register:

Load

DirectCost * Quantity AS Costs

Resident Sales_Register;

Drop table Sales_Register;

Hth

Roland

Re: Problem with a script

sushil353 — Fri, 05 Apr 2013 05:06:01 GMT

Hi,

Try This:

left join(Sales_Register)

LOAD

Product,
DirectCost

FROM
D:\DirectCost.xls
(biff, embedded labels);

final_table:

Load *,DirectCost * Quantity AS Costs

resident Sales_Register;

Drop table Sales_Register;

HTH

Sushil

Re: Problem with a script

er_mohit — Fri, 05 Apr 2013 06:19:03 GMT

hii Try this

Hi,

Try This:

Sales_Register:
LOAD
["_period"] as Period,
'кв.'&ceil(month(date(["_period"]))/3)as Quarter,
Day(Date(["_period"])) as Day,
month(date(["_period"])) as Month,
Year(date(["_period"])) as Year,
["_fld4843rref"] as Product,
["_fld4847rref"] as Distributor,
["_fld4851"] as Quantity,
["_fld4852"] as Sales,

SQL SELECT

"_period",
"_fld4843rref",
"_fld4847rref",
"_fld4851",
"_fld4852"
FROM "ut82sql".public."_accumrg4842";

left join(Sales_Register)

LOAD

Product,
DirectCost

FROM
D:\DirectCost.xls
(biff, embedded labels);

final_table:

Load *,DirectCost * Quantity AS Costs

resident Sales_Register;

Drop table Sales_Register;

Re: Problem with a script

stark___ — Fri, 05 Apr 2013 06:32:36 GMT

Hi Sushil,

Your decision is helpful and it works perfect, but I get a new problem. My direct cost changes every months and if I add new field Period in:

LOAD

Product,

Period,
DirectCost

FROM
D:\DirectCost.xls
(biff, embedded labels);

your decision doesn't work, my Cost shows zero in pivot table.

What must I to do, that Cost shows value correctly?

Best regards, Stark

Re: Problem with a script

kaushiknsolanki — Fri, 05 Apr 2013 06:43:18 GMT

Hi Stark,

This might be because of the new field.

Let me explain how this works, then you are loading the Direct Cost only with field Period, then it joins properly, but when you ass the new field which is Product then you have two fields common in both the tables and this the join doesnt work properly.

So you need to create a linking field and then join them. Sample script could be.

["_fld4847rref"] as Distributor,
["_fld4851"] as Quantity,
["_fld4852"] as Sales,

SQL SELECT

"_period",
"_fld4843rref",
"_fld4847rref",
"_fld4851",
"_fld4852"
FROM "ut82sql".public."_accumrg4842";

left join(Sales_Register)

LOAD

Period&'-'&Product as Key,
DirectCost

FROM
D:\DirectCost.xls
(biff, embedded labels);

Regards,

Kaushik Solanki

Re: Problem with a script

stark___ — Fri, 05 Apr 2013 07:44:23 GMT

Thank you for your explanation. It's helped me.

Best regerds, Stark.