Problem with a script

[stark___]

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

[#2]

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

[sushil353]

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;

HTH

Sushil

[er_mohit]

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;

[stark___]

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

[kaushiknsolanki · Accepted solution]

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.

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,
["_period"]&'-'&["_fld4843rref"] as Key,

["_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

[stark___]

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

Best regerds, Stark.