1 Reply Latest reply: Dec 16, 2016 6:23 AM by Marcus Sommer RSS

    How can I Make Static Dimension

    Majid Ghabooli

      Hi

      Please Guide me

      I have some Table of Supply Chain Management

      as usual measurable variable of them is dynamic , In the other word when you want select specific Amount of Stock should be Type in Experssion ( for instance type if(Trstock_Type_Code=2 and Owner_Type=1 and Purch_Type=1 and Out_Type=1,TrStock_Amount) as Amnt_Wholesale_Gross ) and use similar Filter for dimension until Two sides of the equation to be correct

      Such a calculation is time consuming and not efficient

      How can I make it in Script ... Considering the fact that several table are as follows:

       

      Invoice detail table :

       

      Table name is TrStock :

      LOAD "Good_Code", "Size_Code", "Size_Group_Code", "Stock_Code",

          "TrStock_Amount", "Trstock_DiscPer_Corp", "Trstock_DiscPer_Shop",

          "Trstock_No", "TrStock_Price", "TrStock_Quantity", "Trstock_Type_Code";

       

      SQL SELECT "Good_Code", "Size_Code", "Size_Group_Code", "Stock_Code",

          "TrStock_Amount", "Trstock_DiscPer_Corp", "Trstock_DiscPer_Shop",

          "Trstock_No", "TrStock_Price", "TrStock_Quantity",

          "Trstock_Type_Code"

      FROM NikSport95.dbo.TrStock;

       

      Invoice Header table :

       

      Table name is HTrStock :

      Inner Join ( TrStock )

      LOAD Distinct "Disc_Amnt_Corp","PointM_Amnt", "PointP_Amnt",

          "Disc_Amnt_Shop","Follow_Code", "Purcher_Code","Sender_Code",

          "Follow_Commis_Amnt", "Inv_No", "Out_Type","FB_No","FB_Year",

          "Pay_Date", "Pay_No","Sanad_SNo", "Save_Date",

          "Save_Time", "Save_User", "Seller_Code", "Shop_Commis_Amnt",

          "Stock_Code","Trstock_Date", "Trstock_No", "Trstock_Type_Code",

          Confirm1,"Confirm1_Date", Confirm2, "Confirm2_Date", ConfirmL,"ConfirmL_Date",

          "Rnd_Amnt", "Sum_Amnt","Out_Type" as "OutType_Code";

       

      SQL SELECT "Disc_Amnt_Corp","PointM_Amnt", "PointP_Amnt",

          "Disc_Amnt_Shop", "Follow_Code", "Purcher_Code","Sender_Code",

          "Follow_Commis_Amnt", "Inv_No", "Out_Type","FB_No","FB_Year",

          "Pay_Date", "Pay_No", "Sanad_SNo", "Save_Date",

          "Save_Time", "Save_User", "Seller_Code", "Shop_Commis_Amnt",

          "Stock_Code",  "Trstock_Date", "Trstock_No", "Trstock_Type_Code",

          Confirm1,"Confirm1_Date", Confirm2, "Confirm2_Date", ConfirmL,"ConfirmL_Date",

          "Rnd_Amnt", "Sum_Amnt"

          FROM NikSport95.dbo.HTrStock;

       

      Out_Type :

      left Join ( TrStock )

      LOAD * Inline [

      "Out_Type","Out_Type_Name"

      1,"قطعی"

      2,"انتقالی"

      4,"هدیه"

      5,"اسپانسری"

      6,"ضایعات" ];

       

      Purch_Type :

      left Join ( TrStock )

      LOAD * Inline [

      "Purch_Type","Purch_Type_Name"

      1,"عمده"

      2,"فروشگاهی"

      3,"شوروم"

      4,"ضایعات" ];

       

      Owner_Type :

      left Join ( TrStock )

      LOAD * Inline [

      "Owner_Type","Owner_Type_Name"

      1,"خود مالک"

      2,"دیگر مالک" ];

       

       

      The only solution I found was to create a database qvd ( In fact, a large flat table ) and then write my Condition

      This works, but I am looking for a more efficient and one-stop

       

      thanks for your guide

        • Re: How can I Make Static Dimension
          Marcus Sommer

          In general you need to collect all fields in a single table which should be included in a particular calculation. But this didn't mean mandatory that you need to create a wider table - you could also use a mapping with applymap() to include these fields: Mapping as an Alternative to Joining.

           

          Beside them if your load-times are too large you should consider to load your data with an incremental approach. Here you will find varios links to this topic and some other useful stuff: Advanced topics for creating a qlik datamodel.

           

          Another consideration would be not to do this within the script else within the gui with set analysis in your expressions, maybe something like this:

           

          sum({< Trstock_Type_Code={2}, Owner_Type={1}, Purch_Type={1}, Out_Type={1}>} TrStock_Amount)

           

          - Marcus