6 Replies Latest reply: Apr 5, 2013 3:44 AM by S S RSS

    Problem with a script

    S S

      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

          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
            Sushil Kumar

            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

              • Re: Problem with a script
                S S

                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
                    Kaushik Solanki

                    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

                • Re: Problem with a script
                  Mohit Sharma

                  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;