Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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;
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
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
Thank you for your explanation. It's helped me.
Best regerds, Stark.