Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

stark___
New Contributor III

Problem with a script

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

1 Solution

Accepted Solutions

Re: Problem with a script

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

6 Replies
Not applicable

Re: Problem with a script

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
Honored Contributor II

Re: Problem with a script

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
Honored Contributor II

Re: Problem with a script

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___
New Contributor III

Re: Problem with a script

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

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___
New Contributor III

Re: Problem with a script

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

Best regerds, Stark.

Community Browser